[SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 52번

문제 링크 : https://velog.io/@yooha9621/SQLP실기문제52번
🍎 부분은 내가 생각했을때 튜닝하면서 봐야할 핵심부분을 체크한 곳이다.

1) 데이터 정리

1.주문 상품 테이블

• 🍎월 단위 파티션🍎 테이블
• 한 달 주문상품 = 100만건
• 주문상품 총 건수 = 총 1억 2천만 건 (=100만 X 120개월)
• 할인유형코드 조건을 만족하는 데이터 비중 = 🍎10%🍎

2.상품 테이블

• 등록된 상품 = 2만개
• 🍎할인유형코드 = 'k890' 조건으로 판매되는 상품은 5000여개🍎

2) 기존 쿼리 분석

1. 주문상품 해당 월 파티션 액세스
2. 주문상품_X1 로컬 파티션 인덱스 스캔

• 한 달 동안 주문된 100만건 : '주문일시' 액세스
• 할인 유형 코드가 'k890'인 : '할인유형코드' 스캔
  • 100만건중 10% 즉 , 10만건

2. 1단계의 10만건을 상품 테이블과 NL조인

• 🍎NL조인이 일어나는 횟수 : 10만건🍎

3. 2단계의 20만건을 grouping

• 2만 개 상품을 고르게 주문했으므로 그룹핑하면 약 5000건 추출

4. 3단계의 5천건 Sort

5. 4단계의 5천건 중 상위 100개만 추출

3) 🍎쿼리 튜닝🍎

1.부분 범위 처리

• 상품을 드라이빙 테이블로 놓는다고 가정할 때 할인유형코드 = 'k890' 조건으로 판매되는 상품은 5000여개로 상위 100개가 바로 주문 상품과 NL조인을 한다는 보장이 없다.
• 따라서 '등록일시'가 선두 컬럼인 상품 인덱스를 fast full 스캔하여 해시 맵에 올리고 group by한 5000건의 주문 상품 테이블과 빠르게 해시 조인한다.

2.상품 인덱스 Fast Full 스캔

• 50만건의 속성이 500개인 테이블을 풀스캔하기엔 부하가 너무 크므로 인덱스만 빠르게 full 스캔할 수 있도록 한다.
  • 따라서 상품_X1으로 '등록일시 + 상품코드'인 인덱스를 추가한다.

2.주문 상품 테이블 액세스

• 월 파티션으로 월별 데이터에 접근한다.(즉 , 인덱스에 '주문일시'를 조건절로 굳이 쓸 필요가 없음)
• 할인유형코드 조건으로 10%를 거른다.(인덱스 손익분기점 근접)
  • 따라서 주문 상품 테이블을 Full 스캔할 수 있도록 유도한다.

3.주문상품 - 상품 테이블 조인 방식

• 상품 인덱스를 해시맵으로 놓고 주문상품이 해시 조인을 실행한다.
  • 해시 조인을 하므로 인라인 뷰 안에서 먼저 그룹핑할 필요는 없다.(로우수를 안 줄여도 된다는 소리!)
• 마지막에 추출한 100건으로 다시 상품과 조인하여 상품 정보를 가지고 온다.
  

4) 튜닝한 SQL문

[인덱스 재구성]
주문상품_X1 : 할인유형코드 + 주문일시
[인덱스 추가]
상품_X1 : 등록일시 + 상품코드

SELECT /*+ LEADING(O) USE_NL(O) NO_NLG_BATCHING(P)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액 
FROM ( SELECT /*+ FULL(A) INDEX_FFS(B) LEADING(B) USE_HASH(A) */ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액 , MIN(B.등록일시) 등록일시
  FROM 주문상품 A , 상품 B
  WHERE A.주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND A.할인유형코드 = 'K890'
  AND B.상품코드 = A.상품코드
  GROUP BY A.상품코드 
  ORDER BY 등록일시 DESC) O , 상품 P
WHERE ROWNUM <= 100
AND O.상품코드 = P.상품코드

🍎 정리

• 부분 범위 처리 문제처럼 보이지만 우선은 아니다.
• 50만건 상품중에 5000건만 조인이 되는데 이를 등록일시순으로 정렬한 뒤 5000건을 찾을때까지 NL조인을 시도하는 건 부하가 자칫하면 커질 수도 있기 때문이다.
• 따라서 순서는 다음과 같다.
  • 1. 상품 인덱스 (등록일시 + 상품코드)를 fast full scan 하여 해시맵으로 깔고
  • 2. 할인유형코드 (10%)를 적용한 주문상품 데이터로 하여금 해시조인을 실행 + 그룹화
  • 3. 5000건으로 만든 뒤 소팅하고 100건을 추출해내어 다시 상품과 조인하도록 한다.
• 드라이빙(바깥) 테이블의 로우술를 줄이기 위한 노력은 NL조인에서 하는것!해시조인은 그런 걱정을 할 필요가 없다.