[오라클] 결합인덱스 사용시 성능튜닝

결합인덱스가 Table의 드라이빙 조건으로 사용되는 경우 때에 따라서 성능차이가 

많이 나는 경우가 있는데 아래와 같은 경우가 대표적일 것 같다.

거래 Table과 같이 큰 테이블에  " 거래일자 + 상품코드"로 인덱스가 있을 경우

아래와 같이 조건을 주는 경우이다.

조건: 2014년 01월 ~ 2014년 02월 거래에서 상품코드가 P001인 거래내역의 총 거래 건수 및 거래금액을 구하라... 또는 페이징 처리도 마찬가지..

--------------------------------------------------------------

select 거래일자, count(*) 거래건수, count(amt) 거래금액

from 거래

where 거래일자 between '20140101' and '20140229'

  and 상품코드 = 'P001'

--------------------------------------------------------------

튜닝1. 거래일자는 범위조건이고 상품코드는 등치 (=)  조건임으로 인덱스를 "상품코드 + 거래일자"로 

         하면 됨. 하지만 현식적으로 이미 "거래일자 + 상품코드"가 있음으로 새로 만들지 말고 튜닝2와 

         같이 하자.

튜닝2. "거래일자 + 상품코드" 인덱스가 위와 같은 조건절에서 비효율적인 것은 인덱스 스캔시에

         비효율이 발생하기 때문이다. 결합인덱스의 경우 선두 컬럼이 범위조건 (between, like)이 

         들어오면 뒤의 컬럼은 인덱스 스캔시에 사용되지 못하고 Table Scan 바로 직전에 필터(Filter)

         조건으로 사용됨으로 인덱스 스캔이 제대로 이루어 지지않아 성능이 떨어지는 것임.

         이것을 확인 할려면 거래일자를 바인드 변수로 처리해서 실행계획을 보면 필터 오퍼레이션이

         생김.

--- 튜닝 SQL -------------------------------------------------

SELECT 거래일자, count(*) 거래건수, count(amt) 거래금액

FROM  (

       SELECT TO_CHAR(TO_DATE('201401','YYYYMM') + LEVEL-1,'YYYYMMDD') 일자

       FROM DUAL

       CONNECT BY LEVEL <= TO_DATE('20140228','yyyymmdd') +1 - TO_DATE('20140101','yyyymmdd')

      ) v1

     ,거래 t2

WHERE v1.일자 = t2.거래일자

  AND 상품코드 = 'P001'

--------------------------------------------------------------

위과 같이 일자를 만들어 '='로 조인하게 되면 인덱스스캔시에 상품코드가 사용됨으로 성능이 드라마틱하게 향상됨. 필터 오퍼레이션도 당연히 제거 됨.

---- EOF ----