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

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

1) 데이터 정리

1.주문 상품 테이블

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

2.상품 테이블

• 등록된 상품 = 50만개
• 속성 500개
  • 🍎걍 풀스캔하지 말라는 소리임ㅋㅎ🍎
• 🍎대부분 상품을 한 달에 한 개 이상 주문🍎

2) 기존 쿼리 분석

1. 주문상품_X1 인덱스 스캔

• 한 달 동안 주문된
  • 1억 2천만 건 중 100만건
• 할인 유형 코드가 'k890'인
  • 100만건중 10% 즉 , 10만건

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

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

3. 2단계의 10만건을 grouping

• 50만 개 상품을 고르게 주문하고 그 중 할인 유형 코드가 10%이므로 그룹핑하면 약 5만건 추출(맞나?)

4. 3단계의 데이터 Sort

5. 4단계의 데이터 부분범위처리(상위 100개)

3) 🍎쿼리 튜닝🍎

1.부분 범위 처리

• 상품의 등록일시를 기준으로 부분 범위 처리로 상위 100건을 추출하므로 드라이빙 테이블은 선두컬럼이 '등록일시'인 상품 인덱스로 한다.

2.상품 테이블 액세스

• 등록된 상품 50만개이고 50만 개 상품을 고르게 주문하므로 상품 테이블의 약 대개의 데이터가 액세스되나 속성이 500개므로 풀스캔은 하지 않도록 한다.
• 등록일시가 최신인 순으로 조회 조건을 만족하는 상위 100건을 추출할 수 있도록 부분범위 처리를 해야한다.

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

• 비파티션 테이블에서 1건씩 NL조인으로 100건을 뽑아내므로 인덱스를 사용
  • 조건절은 1) 상품코드 '='조건 , 2) 할인유형코드 '=' 조건 , 3) 기간 '범위' 조건 3가지가 있으므로 조건절이 '액세스 -> 액세스 -> 액세스' 로 처리되게 만든다.
  • 따라서 주문상품_X2로 '상품코드 +할인유형코드 + 주문일시'인 인덱스를 추가한다.

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

• 🍎데이터를 상품 등록일시로 빠르게 정렬하여 상위 100개를 뽑아내야한다.🍎
  • 상품 테이블을 드라이빙 테이블로 놓는다.
• 상품 테이블에서 주문 상품 테이블로 100건 NL 조인을 시도한다.

4) 튜닝한 SQL문

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

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

🍎 정리

• 상품의 등록일시를 기준으로 상위 100건 추출이므로 '등록일시'가 선두컬럼인 인덱스를 가진 상품 테이블을 드라이빙 테이블로 놓고 '상품코드'로 주문 상품 테이블과 NL조인을 처리하도록 만든다.

😥 헷갈렸던 부분

• 상품에서 주문 상품으로 NL 조인을 시도하는 횟수는 최소 100번 최대 50만인데...
  • NL조인 시도 횟수가 덜 할 것이라고 보장하는 부분이 어디인지 모르겠다.
  • 왜냐하면 할인유형코드 'k890'을 만족하는 데이터가 전체의 10%이기 때문이다..
  • 아무리 대부분의 상품을 주문했더라도 1/10 확률로 할인유형코드가 'k890'이기 때문에 조인시도 실패 확률이 크다.
    • 그렇다면 여기서 궁금한 점! NL조인의 부하의 시작점이 어디인걸까? 조인을 시도하는 부분인걸까 조인을 직접 하는 부분인걸까?
• 우선 조인시도에 대한 부하까지는 생각하지 않는 게 좋은 것 같다.