메뉴 건너뛰기

bysql.net

5. 인덱스를 이용한 소트 연산 대체

2011.06.16 02:23

AskZZang 조회 수:4952

05 인덱스를 이용한 소트 연산 대체

 

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로 이를 이용해 소트 오퍼레이션을 생략

 

l  소트 머지 조인에서 outer 테이블을 조인 컬럼에 인덱스가 있을 때 sort merge 오퍼레이션을 생략할 수 있다.

 

l  서브쿼리에서 사용된 테이블이 Unique 인덱스를 갖는다면 unnesting 되었을 때 sort unique 오퍼레이션이 생략된다.

 

l  PK 컬럼에 distinct 쿼리를 수행한다면 sort unique 오퍼레이션이 생략된다.

 

 

(1)  Sort Order By 대체

) [region + custid] 순으로 구성된 인덱스를 사용한는 경우 sort order by 연산 대체

 

SELECT  custid,name,resno,status,tell

FROM    customer a

WHERE   region = 'A'

ORDER   BY custid

 

인덱스가 region 단일 컬럼으로 구성 됐거나

결합인덱스더라도 region 바로 뒤에 custid가 오지 않는다면

ð  Region = ‘A’ 조건을 만족하는 모든 레코드를 인데스를 경우해 읽어야 한다.

 

[region + custid] 순으로 구성된 인덱스를 사용한다면 order by 절을 그대로 둔 상태에서 자동으로 sort order by 오퍼레이션이 제거된다.

이 방식으로 수행한다면 region = ‘A’ 조건을 만족하는 전체 로우를 읽지 않고도 결과집합을 출력 할 수 있으므로 OLTP 환경에서 극적인 성능 개선 효과를 가져다 준다.

ð  소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있는 업무에서만 이 방식이 유리하다.

 

(2)  Sort Group by 대체

) region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용하는 경우

SELECT region, AVG(age), COUNT(*)

FROM   customer

GROUP  BY region

 

실행계획에 ‘sort group by nosort’라고 표시되는 부분을 확인

l  인덱스를 스캔하면서 테이블을 액세스하다가 ‘A’가 아닌 레코드를 만나는 순간 그때까지 집계한 값을 Oracle Net으로 내려 보낸다.

l  이 값은 운반단위에 해당하는 SDU(Session Data Unit)에 버퍼링

l  다시 인덱스를 스캔하다가 ‘B’가 아닌 레코드를 만나는 순간 그때까지 집계한 값을 Oracle Net으로 내려 보낸다.

l  이런 과정을 반복하다가 사용자 Fetch Call에서 요청한 레코드 개수(array size)에 도달하면 전송 명령을 내려 보내고는 다음 Fetch Call이 올 때까지 기다린다.

 

이처럼 인덱스를 이용한 nosort 방식으로 수행될 때는 group by 오퍼레이션에도 불구하고 부분범위처리가 가능해여 OLTP 환경에서 매우 극적인 성능 개선 효과를 얻을 수 있다.

 

(3)  인덱스가 소트 연산을 대체하지 못하는 경우

 

SELECT * FROM emp2 ORDER BY sal;

 

Sal 컬럼을 선두로 갖는 인덱스가 있는데도 정렬을 수행하는 경우.

옵티마이저가 이런 결정을 하는 가장 흔한 원인은

l  인덱스를 이용하지 않는 편이 더 낫다고 판단하는 경우

l  옵티마이저 모드가 all_rows인 경우, 옵티마이저는 전체 로우를 Fetch 하는 것을 기준으로 쿼리 수행 비용을 산정한다. 따라서 데이터량이 많을수록 인덱스를 이용한 테이블 랜덤 액세스 비용이 높아져 옵티마이저는 차라리 Full Table Scan하는 족을 택할 가능성이 높다.

l  옵티마이저 모드를 first_rows로 바꾸면

사용자가 일부만 Fetch 하고 멈출 것임을 시사하므로 옵티마이저는 인덱스를 이용해 정렬 작업을 대체한다.

l  옵티마이저 모드를 변경했음에도 옵티마이저가 계속해서 소트 오퍼레이션을 고집한다면

십중팔구 sal 컬럼에 not null 제약이 정의되어 있지 않을 것이다.

단일 컬럼 인덱스 일 때 값이 null 이면 인덱스 레코드에 포함되지 않기 때문이다.

따라서 인덱스를 이용해 정렬 작업을 대체한다면 결과에 오류가 생길 수 있기 때문이다.

Group by도 마찬가지이다. Group by nosort를 위해 사용하려는 인덱스가 단일 컬럼 인덱스일 때는 해당 컬럼에 not null 제약이 설정돼 있어야 제대로 작동한다.

 

< 인덱스가 있는데도 소트를 대체하지 못하는 사례 >

Null 값이 먼저 출력되도록 하려고 nulls first 구문을 사용하는 경우

 

select /*+ index(e emp_deptno_ename_idx) */ *

from emp e

where deptno = 30

order by ename nulls first

;

결합 인덱스일 때는 null 값을 가진 레코드를 맨 뒤쪽에 저장한다.

따라서 null 값부터 출력하려고 할 때는 인데스를 이용하더라도 소트가 불가피하다.

 

 

select /*+ index(e emp_deptno_ename_idx) */ *

from emp e

where deptno = 30

order by ename nulls last

;

 

select /*+ index_desc(e emp_deptno_ename_idx) */ *

from emp e

where deptno = 30

order by ename desc nulls first

;