펑션을 이용한 튜닝사례

function 을 이용한 SQL tuning 사례

SQL tunig 분야에서 펑션은 일반적으로 제거할 대상이지 권고대상은 아니다.
하지만 예외란 어디에나 있는 법. 어떤 경우에는 펑션이 요긴한 튜닝수단이 될 수도 있는 것이다.

속도문제가 있었던 쿼리의 문제의 where 절 조건은 아래와 같다.

AND 1 = (
         CASE WHEN A.AHEAD_BUY_YN IN ('A','B')
              AND B.OUT_DT >= (SELECT TO_CHAR(ADMIT_DTM, 'YYYYMMDD')
                                 FROM VW_PUMII
                                WHERE PUMI_ID = (SELECT PUMI_ID
                                         FROM XXXX_T_LONG_CNTR
                                        WHERE CNTR_ID = NVL(H.BF_CNTR_ID, H.CNTR_ID))
                                  AND PUMI_STS_CD = '100002') THEN
                   (SELECT 1
                     FROM DUAL
                    WHERE 1 = 1
                      AND B.OUT_DT >= :P_FROM_DT
                      AND B.OUT_DT <= :P_TO_DT )
              ELSE (SELECT 1
                     FROM VW_PUMII
                    WHERE PUMI_ID = (SELECT PUMI_ID
                             FROM XXXX_T_LONG_CNTR
                            WHERE CNTR_ID = NVL(H.BF_CNTR_ID, H.CNTR_ID))
                      AND PUMI_STS_CD = '100002'
                      AND TO_CHAR(ADMIT_DTM, 'YYYYMMDD') >= :P_FROM_DT
                      AND TO_CHAR(ADMIT_DTM, 'YYYYMMDD') <= :P_TO_DT )
          END
          )

일반적으로 WHERE 절에 CASE 문을 쓰는 것은 성능면에서 좋지 않다.
위에서 VW_PUMII 는 몇개의 큰 테이블들이 UNION ALL 로 묶여있는 VIEW 이다. 오라클 옵티마이저가 많은 경우에 좋은 실행계획을 만들어 주지만, 이처럼 복잡한 조건절일 때는 그렇지 못할 때도 많다. 힌트를 여기저기 주어봐도 원하는 실행계획이 만들어지지 않는 것이다.

어떤 사이트에서 만난 위 WHERE 조건이 들어가 있는 쿼리는 10분이상 걸렸다.
특히 CNTR_ID = NVL(H.BF_CNTR_ID, H.CNTR_ID) .. 이런 조건은 튜닝하기 참 난감하다.
이러저러한 방법을 동원해 보고 실패한 뒤에 나는 펑션을 만들어서 풀어내기로 했다.

펑션을 사용하면 한 SQL 에 압축되어 있는 로직을 여러 간단한 SQL 로 느슨하게 풀어낼 수 있기 때문에 옵티마이저로 하여금 어렵지 않게 좋은 실행계획을 만들어 내게 할 수 있다.
하지만, 이것은 어디까지나 특수한 경우에만 사용해야 한다. 왜냐하면 펑션 자체가 갖는 성능 오버헤드가 만만치 않기 때문이다. 그것도 WHERE 절에 붙는 펑션이라면 더욱 더.
해도해도 안되고 펑션을 쓰는게 안쓰는 것보다 이익이 훨씬 많을 때에만 펑션을 쓰면 좋겠다.

위 조건절을 펑션으로 대체한 후 조건절은 아래와 같이 간단해 졌다.
쿼리 수행시간은 5초로 빨라졌다.

AND 1 = FN_CHECK_TEST (A.AHEAD_BUY_YN
                       , B.OUT_DT
                       , H.BF_CNTR_ID
                       , H.CNTR_ID
                       , :P_FROM_DT
                       , :P_TO_DT
                       )

펑션은 아래와 같이 만들면 되겠다.

CREATE OR REPLACE FUNCTION FN_CHECK_TEST (
                        P_AHEAD_BUY_YN VARCHAR2,
                        P_OUT_DT       VARCHAR2,
                        P_BF_CNTR_ID   VARCHAR2,
                        P_CNTR_ID      VARCHAR2,
                        P_FROM_DT      VARCHAR2,
                        P_TO_DT        VARCHAR2) RETURN NUMBER IS
 
  V_AHEAD_BUY_YN  varchar2(30) := P_AHEAD_BUY_YN;
  V_OUT_DT        varchar2(30) := P_OUT_DT;
  V_BF_CNTR_ID    varchar2(30) := P_BF_CNTR_ID ;
  V_CNTR_ID       varchar2(30) := P_CNTR_ID ;
  V_FROM_DT       varchar2(30) := P_FROM_DT ;
  V_TO_DT         varchar2(30) := P_TO_DT ;
 
  V_CNTR_ID2      varchar2(30) := NULL ;
  V_ADMIT_DTM     varchar2(30) := NULL ;
  V_RETURN_VAL    number := NULL;
 
 
BEGIN
 
   V_CNTR_ID2 := NVL(V_BF_CNTR_ID, V_CNTR_ID);
 
   SELECT TO_CHAR(ADMIT_DTM, 'YYYYMMDD')
   INTO V_ADMIT_DTM
   FROM VW_PUMII
   WHERE PUMI_ID = (SELECT /*+ UNNEST */ PUMI_ID
                    FROM XXXX_T_LONG_CNTR
                    WHERE CNTR_ID = V_CNTR_ID2)
   AND PUMI_STS_CD = '100002' ;
 
   IF V_AHEAD_BUY_YN IN ('A','B') AND V_OUT_DT >= V_ADMIT_DTM THEN
 
        SELECT 1
        INTO V_RETURN_VAL
          FROM DUAL
         WHERE 1 = 1
           AND V_OUT_DT >= V_FROM_DT
           AND V_OUT_DT <= V_TO_DT ;
 
    ELSE
 
        SELECT 1
        INTO V_RETURN_VAL
        FROM VW_PUMII
        WHERE PUMI_ID = (SELECT /*+ UNNEST */ PUMI_ID
                         FROM XXXX_T_LONG_CNTR
                         WHERE CNTR_ID = V_CNTR_ID2)
        AND PUMI_STS_CD = '100002'
        AND TO_CHAR(ADMIT_DTM, 'YYYYMMDD') >= V_FROM_DT
        AND TO_CHAR(ADMIT_DTM, 'YYYYMMDD') <= V_TO_DT ;
 
    END IF;
 
    RETURN V_RETURN_VAL;
 
EXCEPTION
 
   WHEN OTHERS THEN RAISE ;
 
END FN_CHECK_TEST;
/