[SQLP실기풀이]5장 SQL옵티마이저(3)-쿼리 변환 33번

문제 링크 : https://velog.io/@yooha9621/SQLP실기문제5장-SQL옵티마이저3-쿼리-변환-32번-2e6ma40m

1) 해설

• Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리된다.
• 그리고 대개 실행계획 상에서 맨 마지막 단계에 처리된다.
• 서브쿼리 필터링을 먼저 처리함으로써 조인 단계로 넘어가는 로우 수를 크게 줄일 수
  있다면 성능은 그만큼 향상된다.
• ⭐️주문 테이블과 조인하기 전에 서브쿼리 필터링을 먼저 처리하도록 push_subq 힌트를 사용한다.⭐️

2) 기존 SQL 실행계획

1. 상품으로부터 주문 테이블로 1,800번의 조인 액세스
2. 조인에 성공한 주문 데이터는 60,000개 , 조인 과정에 38,897개 블록을 읽었다.
3. 60,000개 조인 결과집합 생성
4. 서브쿼리 필터링을 수행하고 나서 3,000개로 줄었다.
5. 총 읽은 블록 수는 38,103이다.

기존

• 상위분류코드가 'AK'인 상품의 주문 정보를 집계해서 1개의 로우를 출력하도록 함.
• 주문일시를 만족하는 데이터 6만건 (인덱스도 있음)
• 상품은 풀스캔해서 1000건 나옴
• 상품이랑 NL조인해서 6만건 나옴
• 6만건으로 상품분류코드 하나씩 찌름 > 줄여야한다.

3) 튜닝한 SQL문

select /*+ leading(p) use_nl(t) */
count(distinct p. 상품번호), sum(t. 주문금액)
, sum(t.주문수량), avg(t.할인율)
from 상품 p, 주문 t
where t. 상품번호 = p. 상품번호
and t. 주문일시 >= trunc(sysdate - 7)
exists( select /*+ no_unnest push_subq */ 'x'
from 상품분류
where 상품분류코드 = p.상품분류코드
and 상위분류코드 = 'AK' );

4) 오답 정리

• ⭐️ 서브쿼리는 캐싱 기능이 있다는 것을 잊지말자 ⭐️
  • 상품분류 인덱스 추가 : 테이블 액세스를 막기 위해 상품분류코드+상위분류코드로 구성된 인덱스를 추가했다.하지만 상품분류는 데이터가 소량이어서 필터 과정에 캐싱 기능이 효과적으로 작동하고 있으므로 ⭐️굳이 인덱스를 추가할 필요가 없다.⭐️
• push_subq 힌트 사용 상황을 잊지말자
  • unnesting되지 않는 서브쿼리를 먼저 수행하고 싶을 때
    • ⭐️ /*+ no_unnest push_subq */ ⭐️