SELECT-LIST 컬럼 가공시 정렬연산 수행 확인 및 개선방법

인덱스가 Id,  ch_date, ch_order 순으로 생성되어 있을 경우 MIN 값을 구해도 별도의 정렬연산을 수행하지 않는다. 수직적 탐색을 통해서 가장 왼쪽지점에서 보는 최소 값이 바로 구하고자 하는 값이기 때문이다.


1
SELECT MIN(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

MAX의 경우도 마찬가지이다. MIN과 다른 점은 왼쪽에서 찾는게 아니라 가장 오른쪽에 있는 데이터를 찾는다는 점이다.

1
SELECT MAX(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

그래서 두 개의 실행계획을 살펴보면 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽 (MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.

1
SELECT MAX(TO_DATE(ch_date)) FROM scott.SORT_TEST WHERE ID = 'C';
cs

이 경우에는 MAX일지라도 ch_date가 내부적으로 변경을 한 뒤에 최대값을 찾기 때문에 정렬을 한뒤에 진행이 가능하다.


하지만 이걸 반대로 바꿔서 진행하면 최대 값을 찾고 그 값을 TO_DATE()로 변경하기 때문에 큰 문제 없이 FIRST 항목만 찾게된다.

출처 : 친절한 SQL 튜닝

댓글()
  1. 2019.03.30 14:54 댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

인덱스를 이용한 sort 연산 생략

인덱스 Range Scan이 가능하려면 가공하려는 컬럼의 데이터가 정렬되어 있어야 가능하다. 인덱스는 그렇기 때문에 정렬이 되어있다. 그래서 인덱스를 사용하는 이유가 있다. 

테이블 생성

1
2
3
4
create table sort_test (
id char,
ch_date date,
ch_order varchar(10));
cs


제약조건 추가

1
ALTER TABLE SCOTT.SORT_TEST ADD CONSTRAINT ODER_PRIMARY PRIMARY KEY (ID,CH_DATE,CH_ORDER);
cs


데이터


실행계획

PK를 기준으로 알아서 정렬이 되어있다. ( ID -> CH_DATE -> CH_ORDER 순서대로)


만약 별도로 order by를 sql에 지정한다고 해도 옵티마이저는 sort order by를 진행하지 않는다. 왜냐면 이미 pk 인덱스 생성시에 결과 집합에 대한 order by가 된 상태로 진행되기 때문이다.


만약 이렇게 정렬 연산을 생략가능하게 구성되어 있지 않다면 SORT ORDER BY가 추가 될 것이다.

인덱스에서 값을 찾을 때 인덱스 리프 블록에서 각 블록들은 더블 링크드 리스트로 연결되어있다. ASC 정렬인 경우에는 왼쪽에서 오른쪽 DESC인 경우에는 오른쪽에서 왼쪽으로 진행된다. 

각 DESC (내림차순)으로 사용하도록 쿼리를 작성하고 실행계획을 확인해도 별도의 SORT 작업은 진행하지 않는것을 알 수있다.

ORDER BY를 이용한 정렬 컬럼 가공
인덱스에서 사용되는 컬럼을 가공하면 INDEX가 타지 않는다고 알고있다. 근데 앞에서 살펴본 부분은 where조건에서 컬럼을 가공한 경우였다.

이번에는 인덱스에서 선언한 구조와 반대되는 쿼리를 짜보자. 위의 쿼리에서 PK 인덱스는 ID,CH_DATE,CH_ORDER 순서로 인덱스를 만들었다. 그렇기 때문에 ID -> CH_DATE -> CH_ORDER 순서로 정렬을 하면서 데이터를 찾는다.

하지만 다음과 같은 쿼리는 어떨까?

생성된 인덱스와 다른 기준으로 CH_DATE -> CH_ORDER 으로 정렬을 진행하였기 때문에 다시 정렬연산이 필요로 하게된다.


정리하면 인덱스 생성시에 sort가 되기 때문에 order by를 하여도 별도의 sort 연산을 하지 않는다.


댓글()

시퀀셜 액세스와 랜덤 액세스

데이터베이스에서 데이터를 블록단위로 읽는다. 1 ~ 3 byte와 같이 작은 데이터를 읽을때도 하나의 블록을 읽어들인다. 그리고 테이블뿐만 아니라 인덱스도 블록단위로 읽어들인다.


데이터베이스의 총 블록 사이즈를 알고 싶으면 다음 쿼리를 통해 확인해 볼 수있다.

1
select value from v$parameter where name = 'db_block_size';
cs


그럼 테이블과 인덱스를 블록단위로 읽는 방식에 대해서 알아보자.


테이블 또는 인덱스를 읽는 방식


시퀀셜 액세스 (Sequential Access)

논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽어들이는 방식이다. 인덱스 리프블록은 앞뒤를 가리키는 주소값으로 서로 연결되어 있는데 이를 이용하여 순차적으로 스캔하는 방식이다.

테이블 블록은 서로 논리적으로 서로 연결고리를 가지고 있지는 않지만 세그먼트 헤더가 각 익스텐트들에 대한 주소를 맵의 형태로 가지고 있어 그를 이용하여 Table Full Scan이 가능하도록 하는 구조이다.


랜덤 액세스 (Random Access)

논리적, 물리적 순서가 아닌 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.



그림을 보면 알겠지만, 시퀀셜 액세스의 경우 각 엘리먼트들의 그룹을 순차적으로 접근하는 반면에 랜덤 액세스는 조각으로 쪼개져있으며 저장이 가능한 공간에 저장되어 있어서 필요한 블록을 읽어 들일때마다 한 블록씩 접근해야 한다.

댓글()