[SQLP실기풀이]6장 고급SQL튜닝(1)-소트튜닝12번

문제 링크 : https://velog.io/@yooha9621/SQLP실기문제-소트튜닝12번

1) 데이터 정리

1.상품 테이블

• 상품 총 건수 = 총 1,000건
• 상품유형코드 '=' 조건을 만족하는 평균 카디널리티는 100 ( 약 10% )

2.계약 테이블

• 1년 주문상품 = 500만건
• 계약 총 건수 = 총 5,000만 건 (=500만 X 10년)
• 상품유형코드 '=' 조건을 만족하는 평균 카디널리티는 약 10%

2) 기존 쿼리 분석

1. 상품_X1(상품유형코드) 인덱스 통해 액세스
2. 1단계의 100건을 계약_X2(상품번호) 인덱스 통해 NL 조인
3. 2단계의 100건 중복 제거 Sort

🤔 처음 생각했던 튜닝 포인트

1. 상품 정보를 조회하는 쿼리이므로 중복 제거 Sort를 없애고 바로 상품 테이블에서 조회하도록 바꾼다.(즉,조회시 DISTINCT는 쓰지 않는다.)
2. 상품 테이블 스캔시 상품유형코드 '=' 조건을 만족하는 비율이 10%이므로 액세스할 때 인덱스 액세스가아닌 Full 액세스로 바꾼다.
3. 상품을 테이블 풀 스캔하는데 전체 1000건밖에 없으므로 상품 테이블을 해시맵으로 깔고 해시 조인으로 진행한다.

⭐️ 답안 튜닝 포인트

1. 상품 정보를 조회하는 쿼리이므로 중복 제거 Sort를 없애고 바로 상품 테이블에서 조회하도록 바꾼다.(즉,조회시 DISTINCT는 쓰지 않는다.)
2. NL조인시 100건만 액세스 할 수 있으므로 굳이 해시조인으로 넘기려고 하지는 않는다.
3. 따라서 상품 테이블을 '상품유형코드'조건으로 액세스 할 수 있도록 인덱스를 사용하고 계약 상품과 조인을 시도할 때 세미 조인으로 시도한다.
   • 또는 조인을 하지 않고 EXISTS 구문을 사용하도록 한다.

3) 쿼리 튜닝

1.상품 테이블 액세스

• 상품 테이블을 상품유형코드 '=' 조건으로 조회하도록 상품_X1 인덱스를 사용한다.
• 상품 테이블을 먼저 스캔한다.
  

2.계약 테이블 액세스

-상품 테이블과 '상품번호'로 세미 조인(또는 EXIST문)하면서 동시에 계약기간도 비교해야하므로 인덱스를 상품번호 + 계약 기간으로 재구성한다.

• 계약_X2 : 상품번호 + 계약기간

2) 튜닝한 SQL문

[인덱스 재구성]
계약_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))
            );

🍎 정리

• 중복 소트를 없애기 위해 상품 테이블을 드라이빙 테이블로 두고 계약 테이블을 100건만 찌를 수 있도록 계약 테이블에 존재 여부만 조회하도록 바꾼다.

✅ 조심할 부분

• 해시 조인을 쓰는 목적을 제대로 알고 쓸 줄 알아야한다.
• ⭐️ 즉 , NL조인을 하기엔 액세스 량이 너무 많은 경우 !! 그 대안으로 해시 조인을 쓰도록 하자!!