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

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

1) 데이터 정리

1.주문 상품 테이블

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

2.상품 테이블

• 등록된 상품 = 2만개
• 🍎대부분 상품을 한 달에 한 개 이상 주문🍎

2) 기존 쿼리 분석

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

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

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

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

4. 3단계의 20만건을 grouping

• 대부분 상품을 한달에 한 개 이상 주문했으므로 그룹핑하면 약 2만건 추출

5. 4단계의 2만건 Sort

6. 🍎5단계의 2만건 중 100건 부분 범위 처리🍎

3) 🍎쿼리 튜닝🍎

1. 부분 범위 처리

• '총 주문금액'으로 정렬한 2만건을 100건으로 효율적으로 추려내야한다.

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

• 100만건의 파티션 테이블에서 20만건을 뽑아내므로 Full Scan을 유도한다.

3.상품 테이블 액세스

• 등록된 상품 2만개중에 월별로 대부분의 상품을 주문하지만 총 100건만을 추출하는 부분범위처리가 진행된다.
  • 따라서 마지막 단계에서 100건 추출시 상품 테이블을 NL 조인하도록 한다.

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

• ⭐️ 우선 20만건의 NL조인을 다른 방법의 조인으로 바꿔야한다. ⭐️
• 주문 상품 테이블 따로 그룹핑을 하여 조인 대상 로우수를 2만건으로 줄인 후 소팅을 실행한다.
• 소팅 실행 후 100건만 우선 추출하도록 한다.
• 추출된 100건을 상품과 NL 조인한다.
  

4) 튜닝한 SQL문

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

또는 (NO_NLG_BATCHING 힌트를 사용하지 않는 경우 order by를 한 번 더 수행)

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

🍎 정리

• 주문 상품을 Full스캔한 뒤 그룹핑하여 로우수를 줄이고
  줄인 로우수 100건으로 상품 테이블과 NL조인을 한다.

✅ NO_NLG_BATCHING(테이블명) 힌트 사용하기

• 조인과정 중에 인라인 뷰 안에서 정렬된 결과가 뒤섞이지 않도록 한다.
• 테이블명안에는 조인을 시도할 테이블명이 들어간다.