SQL 튜닝이론 - 서브쿼리 인라인 뷰의 최적화방법 뷰머징과 조건절 밀어넣기

SQL 튜닝이론 - 서브쿼리 인라인 뷰의 최적화방법 뷰머징과 조건절 밀어넣기

 

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화합니다. 아래 쿼리를 예로 들면, 옵티마이저는 뷰를 독립적으로 최적화하기 위해 당월 거래 전체를 읽어 고객번호 수준으로 GROUP BY 하는 실행계획을 수립합니다. 고객 테이블과 뷰의 조인은 그다음 처리합니다.

 

-- 고객_x1 : 가입일시
-- 거래_x1 : 거래일시
SELECT C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래,
FROM 고객 C
, (
  SELECT 고객번호, AVG(거래금액) 평균거래
  , MIN(거래금액) 최소거래, MAX(거래금액) 최대거래
  FROM 거래
  WHERE 거래일시 >= TRUNC(SYSDATE, 'MM') -- 당월 발생한 거래
  GROUP BY 고객번호
) T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
AND T.고객번호 = C.고객번호

-- EXECUTION PLAN
-- 0    SELECT STATEMENT
-- 1  0     NESTED LOOPS
-- 2  1       NESTED LOOPS
-- 3  2         VIEW
-- 4  3           HASH (GROUP BY)
-- 5  4             TABLE ACCESS BY INDEX ROWID OF '거래'
-- 6  5               INDEX RANGE SCAN OF '거래_X01'
-- 7  2         INDEX RANGE SCAN OF '고객_X01'
-- 8  1       TABLE ACCESS BY INDEX ROWID OF '고객'

 

성능상 문제가 되는 부분은 고객 테이블에서 '전월 이후 가입한 고객'을 필터링하는 조건이 인라인 뷰 바깥에 있다는 사실입니다. 이 조건이 있음에도 인라인 뷰 안에서는 모든 고객에 대한 당월 거래 데이터를 읽고 있습니다. 아래의 쿼리는 merge 힌트를 이용해 뷰를 메인 쿼리와 머징합니다. 참고로, 뷰 어짐을 방지하고자 할 땐 no_merge 힌트를 사용합니다.

 

-- 고객_x1 : 가입일시
-- 거래_x2 : 고객번호 + 거래일시
SELECT C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래,
FROM 고객 C
,(
  SELECT /*+ merge */
  고객번호, AVG(거래금액) 평균거래, MIN(거래금액) 최소거래, MAX(거래금액) 최대거래
  FROM 거래
  WHERE 거래일시 >= TRUNC(SYSDATE, 'MM') -- 당월 발생한 거래
  GROUP BY 고객번호
) T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
AND T.고객번호 = C.고객번호

-- EXECUTION PLAN
-- 0    SELECT STATEMENT
-- 1  0     HASH (GROUP BY)
-- 2  1       NESTED LOOPS
-- 3  2         TABLE ACCESS BY INDEX ROWID OF '고객'
-- 4  3           INDEX RANGE SCAN OF '고객_X01'
-- 5  2         TABLE ACCESS BY INDEX ROWID OF '거래'
-- 6  5           INDEX RANGE SCAN OF '거래_X02'

 

실행계힉을 보면, 쿼리가 아래와 같이 변환되었음을 알 수 있습니다.

 

SELECT C.고객번호, C.고객명, AVG(t.거래금액) 평균거래, MIN(t.거래금액) 최소거래, MAX(t.거래금액) 최대거래
FROM 고객 C, 거래 T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
AND T.고객번호 = C.고객번호
AND T.거래일시 >= TRUNC(SYSDATE, 'MM') -- 당월 발생한 거래
GROUP BY C.고객번호, C.고객명

 

가장 먼저 액세스 하는 고객_x01 인덱스는 가입일시가 선두 칼럼입니다. 고객 테이블을 INDEX RANGE SCAN 한 사실을 통해 이를 짐작할 수 있습니다. 거래_X02 인덱스는 [고객번호 + 거래일시] 순으로 구성돼 있어야 최적인데, 그렇게 구성돼 있다고 가정합니다.

 

실행계획을 보면, 고객 테이블을 먼저 읽습니다. 인덱스를 이용해 전월 이후 가입한 고객만 읽고, 거래 테이블과 조인할 때는 해당 고객들에 대한 당월 거래만 읽습니다. 거래 테이블을 [고객번호 + 거래일시] 순으로 구성된 인덱스를 이용해 NL 방식으로 조인하기 때문에 가능합니다. 단점은 조인에 성공한 전체 집합을 GROUP BY 하고서야 데이터를 출력할 수 있어 부분범위 처리가 불가능하다는 점입니다. 만약 전월 이후 가입한 고객이 매우 많고 당월 거래도 매우 많다면, 부분범위 처리가 불가능한 상황에서 NL 조인은 좋은 선택이 아닙니다.

 

부분범위 처리가 불가능한 상황에선 보통 해시 조인이 빠른데, 아래는 뷰 머 징한 거래 테이블을 고객과 해시 조인 한 후에 GROUP BY 하는 실행계획이다. 물론 고객과 거래 테이블을 읽는 과정에 각각 인덱스를 사용할 수도 있습니다.

 

-- EXECUTION PLAN
-- 0    SELECT STATEMENT
-- 1  0     HASH (GROUP BY)
-- 2  1       HASH JOIN
-- 3  2         TABLE ACCESS (FULL) OF '고객'
-- 4  2         TABLE ACCESS (FULL) OF '거래'

 

조인조건 pushdown

 

오라클 11g 부터 조인조건 pushdown 힌트를 통해 퀴리 변환을 유도할 수 있습니다. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능입니다. 아래 실행계획에 나타는 VIEW PUSHED PREDICATE 오퍼레이션을 통해 이 기능의 작동 여부를 확인할 수 있습니다.

 

SELECT C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래,
FROM 고객 C
, (
  SELECT /*+no_merge push_pred*/
  고객번호, AVG(거래금액) 평균거래
  , MIN(거래금액) 최소거래, MAX(거래금액) 최대거래
  FROM 거래
  WHERE 거래일시 >= TRUNC(SYSDATE, 'MM') -- 당월 발생한 거래
  GROUP BY 고객번호
) T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
AND T.고객번호 = C.고객번호

-- EXECUTION PLAN
-- SELECT STATEMENT
--  NESTED LOOPS
--    TABLE ACCESS BY INDEX ROWID BATCHED OF '고객'
--      INDEX (RANGE SCAN) OF 고객_X01
--    VIEW PUSHED PRDICATE
--      SORT(GROUP BY)
--        TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래'
--          INDEX RANGE SCAN OF '거래_X02'

 

아래는 허용되지 않는 문법이지만, 인라인뷰로 메인쿼리의 조건절을 밀어 넣는다는 것은 옵티마이저가 내부에서 쿼리를 이와 같은 형태로 변환해서 최적화했다고 이해하기 쉽습니다.

 

SELECT C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래,
FROM 고객 C
, (
  SELECT /*+no_merge push_pred*/
  고객번호, AVG(거래금액) 평균거래
  , MIN(거래금액) 최소거래, MAX(거래금액) 최대거래
  FROM 거래
  WHERE 거래일시 >= TRUNC(SYSDATE, 'MM')
  AND 고객번호 = C.고객번호
  GROUP BY 고객번호
) T -- 당월 발생한 거래
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객

 

이 방식을 사용하면 전월 이후 가입한 고객을 대상으로 '건건이' 당월 거래 데이터만 읽어서 조인하여 GROUP BY를 수행합니다. GROUP BY 가 전체 결과에 대해 이뤄지는 것이 아니므로 중간에 멈출 수도 있습니다. 즉, 부분범위 처리가 가능합니다. 뷰를 독립적으로 실행할 때처럼 당월 거래를 모두 읽지 않아도 되고, 뷰를 머징할 때처럼 조인에 성공한 전체 집합을 GROUP BY 하지 않아도 됩니다. 이 기능을 제어하는 힌트는 'push_pred'입니다. 옵티마이저가 뷰를 머징하면 힌트가 작동하지 않으니 no_merge 힌트를 항상 함께 사용해야 합니다.