[SQLP] 핵심노트2) 6.고급SQL튜닝1_12번

12. 아래 SQL을 튜닝하시오

-. 필요시 인덱스 재구성안을 제시하시오

-. 원하는 실행계획이 나오도록 힌트를 정확히 기술하시오

[데이터] 상품 : 1000건 계약 : 5000만건 ▶ 1년 간 계약 건수는 500만 건 ▶ 상품유형코드를 '=' 조건으로 검색할 때의 평균 카디널리티는 100 [인덱스 구성] 상품_PK : 상품번호 상품_X1 : 상품유형코드 계약_PK : 계약번호 계약_X1 : 계약일자 계약_X2 : 상품번호

SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, P.상품분류코드
FROM 상품 P, 계약 C
WHERE P.상품유형코드 = :PCLSCD
AND C.상품번호 = P.상품번호
AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE,-12))

 

★풀이

-. 상품유형코드 선택도(Selectivity) = 10%(100/1000)

                                              카디널리티 / 총  레코드 수

-. 최근계약건수가 500만건, 이 중의 10%인 50만건을 읽을 것으로 예상 됨

 

※튜닝포인트

-. column 값에 있는 P.상품번호가 Distinct한 값이다.

-. P.상품유형코드로 조회한, DISTINCT한 값 100개에 대해서만 계약 Table과 조인에 성공하는 데이터가 존재 여부만 확인 하는 조건으로 만든다, (EXISTS 서브쿼리로 변환)

-. 계약일자를 조회할때 테이블 랜덤엑세스를 피하기 위해서는 INDEX 계약_X2=상품번호 + 계약일자로 변경

 

[인덱스 재구성]
계약_X2 : 상품번호 + 계약기간

-- NL_SJ 조인시
SELECT /*+ LEADING(P) */
P. 상품번호, P. 상품명, P.상품가격, P.상품분류코드
FROM 상품 P
WHERE
P.상품유형코드 = :PCLSCD
AND EXISTS ( SELECT /*+ UNNEST NL_SJ */ 'X'
            FROM 계약 C
            WHERE C.상품번호 = P.상품번호
            AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12))
            );

-- 조인시도 안하고 EXSISTS 문으로 실행시
SELECT /*+ LEADING(P) */
P. 상품번호, P. 상품명, P.상품가격, P.상품분류코드
FROM 상품 P
WHERE
P.상품유형코드 = :PCLSCD
AND EXISTS ( SELECT /*+ NO_UNNEST */ 'X'
            FROM 계약 C
            WHERE C.상품번호 = P.상품번호
            AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12))
            );