SQL 튜닝이론 - 서브쿼리 UNNESTING 과 PUSHING

 

SQL 튜닝이론 - 서브쿼리 UNNESTING 과 PUSHING

 

UNNESTING 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대부분 실행계획 상에서 맨 마지막 단계에 처리됩니다. 예를 들어, 아래는 상품과 주문 테이블을 조인하고 나서 서브쿼리 필터링을 수행할 때의 트레이스 결과입니다.

 

SELECT /*+LEADING(P) USE_NL(T)*/
COUNT( DISTINCT P.상품번호) , SUM(T.주문금액)
FROM 상품 P, 주문 T
WHERE P.상품번호 = T.상품번호
AND P.등록일시 >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
AND T.주문일시 >= TRUNC(SYSDATE - 7)
AND EXISTS (
  SELECT 'X'
  FROM 상품분류
  WHERE 상품분류코드 = P.상품분류코드
  AND 상위분류코드 = 'AK'
)

-- ROWS       ROW SOURCE OPERATION
--    0       STATEMETN
--    1       SORT AGGREGATE                                  (cr=38103) -- 4
--  3000        FILTER                               -- 1
--  60000         NESTED LOOPS                       -- 2     (cr=38097) -- 5
--  1000            TABLE ACCESS FULL 상품            -- 3
--  60000           TABLE ACCESS BY INDEX ROWID 주문
--  60000             INDEX RANGE SCAN 주문_PK
--      1         TABLE ACCESS BY INDEX ROWID 상품분류
--      3           INDEX UNIQUE SCAN 상품분류_PK

 

쿼리의 실행결과를 보면 상품으로부터 주문 테이블로 1000번의 조인 액세스가 있었습니다(3). 조인에 성공한 주문 데이터는 60,000 개 입니다(2). 조인 과정에서 38,097 개 블록을 읽었습니다(5). 60,000개 조인 결과집합은 서브쿼리 필터링을 수행하고 나서 3,000 개로 줄어듭니다(1). 결과적으로 총 읽은 블록 수는 38,103 입니다(4).

 

트레이스 분석결과, 대부분 I/O가 조인 과정에서 발생했습니다. 만약 서브쿼리 필터링을 먼저 처리함으로써 조인 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능이 그만큼 향상됩니다. 아래는 주문 테이블과 조인하기 전에 서브쿼리 필터링을 먼저 수행할 때의 트레이스 결과입니다. 서브쿼리 필터링을 먼저 처리하기 위해 push_subq 힌트를 사용합니다.

 

SELECT /*+LEADING(P) USE_NL(T)*/
COUNT( DISTINCT P.상품번호) , SUM(T.주문금액)
FROM 상품 P, 주문 T
WHERE P.상품번호 = T.상품번호
AND P.등록일시 >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
AND T.주문일시 >= TRUNC(SYSDATE - 7)
AND EXISTS (
  SELECT /*+NO_UNNEST PUSH_SUBQ*/
  'X'
  FROM 상품분류
  WHERE 상품분류코드 = P.상품분류코드
  AND 상위분류코드 = 'AK'
)

-- ROWS       ROW SOURCE OPERATION
--    0       STATEMET
--    1       SORT AGGREGATE                                  (cr=1903) -- 3
--  3000         NESTED LOOPS                                 (cr=38097)
--  150           TABLE ACCESS FULL 상품            -- 1
--    1             TABLE ACCESS BY INDEX ROWID 상품분류
--    3               INDEX UNIQUE SCAN 상품분류_PK
--  3000          TABLE ACCESS BY INDEX ROWID 주문   -- 2
--  3000             INDEX RANGE SCAN 주문_PK

 

서브쿼리를 필터링한 결과가 150건 이므로 주문 테이블과의 조인 횟수도 150번으로 줄어듭니다(1). 주문 데이터도 3000개 만 읽습니다.(2) 총 읽은 블록 수도 1903 개로 줄어듭니다.(3)

 

서브쿼리 PUSHING은 이처럼 서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능입니다. 'push_subq / no_push_subq' 힌트로 제어합니다. 이 기능은 UNNESTING 되지 않은 서브쿼리에만 작동합니다. 서브쿼리가 UNNESTING 되면 필터가 아닌 다양한 조인 방식으로 실행됩니다. UNNESTING 되는 순간, push_subq로 서브쿼리 pusing을 유도할 수 없습니다. 따라서 push_subq 힌트와 no_unnest 힌트를 동시에 기술하는 것이 올바른 사용법입니다. PUSING 서브 쿼리와 반대로 서브쿼리 필터링을 가능한 한 나중에 처리하게 하려면 no_unnest와 No_push_subq를 같이 사용하면 됩니다.