[Tuning] 분석함수의 활용 (셀프조인의 해결)

Analytic Functions 의 활용
-> 셀프 조인의 해결 #1

Original Source -- View Merge 현상 발생

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM CUSTOMER C,
     /* 최종상태*/
     (SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
        FROM CUSTOMER B,
           /* 최종날짜 */
           (SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
              FROM CUSTOMER
             WHERE STSDAT <= '20031130'
            GROUP BY BRNCOD, CMPID) A
        WHERE B.BRNCOD = A.BRNCOD
          AND B.CMPID  = A.CMPID
          AND B.STSDAT = A.MAX_DAT
        GROUP BY B.BRNCOD, B.CMPID, B.STSDAT) D
WHERE C.BRNCOD = D.BRNCOD
  AND C.CMPID  = D.CMPID
  AND C.STSDAT = D.STSDAT
  AND C.STS    = D.MAX_STS
  AND C.AMT > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          2         12          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          2         12          0           4

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      4   FILTER
      8    SORT (GROUP BY)
     16     TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     41      NESTED LOOPS
      8       NESTED LOOPS
      4        VIEW <---------------------------
      4         SORT (GROUP BY)
     20          TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     20           INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
      8        TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     16         INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
     32       INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)

Tuning-01 (order by절 추가하여 View merge 현상 해결)

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM CUSTOMER C,
          /* 최종상태*/
     (SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
        FROM CUSTOMER B,
                /* 최종날짜 */
           (SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
              FROM CUSTOMER
             WHERE STSDAT <= '20031130'
            GROUP BY BRNCOD, CMPID) A
        WHERE B.BRNCOD = A.BRNCOD
          AND B.CMPID  = A.CMPID
          AND B.STSDAT = A.MAX_DAT
        GROUP BY B.BRNCOD, B.CMPID, B.STSDAT
        ORDER BY B.BRNCOD, B.CMPID, B.STSDAT) D <- order by를 추가해서 view merge 현상을 해결
WHERE C.BRNCOD = D.BRNCOD
  AND C.CMPID  = D.CMPID
  AND C.STSDAT = D.STSDAT
  AND C.STS    = D.MAX_STS
  AND C.AMT > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1         12          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1         12          0           4

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      4   NESTED LOOPS
      4    VIEW
      4     SORT (GROUP BY)
      8      TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     21       NESTED LOOPS
      4        VIEW
      4         SORT (GROUP BY)
     20          TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     20           INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2'(NON-UNIQUE)
     16        INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
      4    TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
      4     INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)

서브쿼리를 줄이기

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM CUSTOMER C,
      (SELECT BRNCOD, CMPID,
              SUBSTR(MAX(STSDAT||STS),1,8) MAX_DAT,
              SUBSTR(MAX(STSDAT||STS),9,1) MAX_STS
         FROM CUSTOMER
        WHERE STSDAT <= '20031130'
        GROUP BY BRNCOD, CMPID
        ORDER BY BRNCOD, CMPID) B
WHERE C.BRNCOD = B.BRNCOD
  AND C.CMPID  = B.CMPID
  AND C.STSDAT = B.MAX_DAT
  AND C.STS    = B.MAX_STS
  AND C.AMT > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          9          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          9          0           4

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      4   NESTED LOOPS
      4    VIEW
      4     SORT (GROUP BY)
     20      TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
     20       INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
      4    TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
      4     INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)

분석함수 사용하기
- MAX(~) KEEP(DENSE RANK LAST ORDER BY column ASC)

SELECT BRNCOD, CMPID,
       MAX(STSDAT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC , STS ASC) A,
       MAX(STS) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) B,
       MAX(AMT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) C
  FROM CUSTOMER
 WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID

분석함수 사용하기
- MAX(~) KEEP(DENSE RANK FIRST ORDER BY column DESC)

SELECT BRNCOD, CMPID,
       MAX(STSDAT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) A,
       MAX(STS) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) B,
       MAX(AMT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) C
  FROM CUSTOMER
 WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID

분석함수 사용
- FIRST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ DESC WINDOWING 절)

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
              FIRST_VALUE(STSDAT)
              OVER(PARTITION BY BRNCOD, CMPID
                   ORDER BY STSDAT DESC, STS DESC
                   ROWS UNBOUNDED PRECEDING) MAX_DAT,
              FIRST_VALUE(STS)
              OVER(PARTITION BY BRNCOD, CMPID
                   ORDER BY STSDAT DESC, STS DESC
                   ROWS UNBOUNDED PRECEDING) MAX_STS,
              AMT
         FROM CUSTOMER
        WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
  AND C.STS    = C.MAX_STS
  AND C.AMT > 0;

분석함수 사용 - 하나로 합치기

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
               FIRST_VALUE(STSDAT||STS)
               OVER(PARTITION BY BRNCOD, CMPID
                    ORDER BY STSDAT||STS DESC
                    ROWS UNBOUNDED PRECEDING) MAX_DTSTS,
               AMT
          FROM CUSTOMER
         WHERE STSDAT <= '20031130') C
 WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
   AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
   AND C.AMT > 0;

분석함수 사용
- LAST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ASC  WINDOWING 절)

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
              LAST_VALUE(STSDAT)
              OVER(PARTITION BY BRNCOD, CMPID
                   ORDER BY STSDAT ASC, STS ASC
                   ROWS BETWEEN CURRENT ROW
                            AND UNBOUNDED FOLLOWING) MAX_DAT,
              LAST_VALUE(STS)
              OVER(PARTITION BY BRNCOD, CMPID
                   ORDER BY STSDAT ASC, STS ASC
                   ROWS BETWEEN CURRENT ROW
                            AND UNBOUNDED FOLLOWING) MAX_STS,
              AMT
         FROM CUSTOMER
        WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
  AND C.STS    = C.MAX_STS
  AND C.AMT > 0;

분석함수 사용 - 하나로 합치기

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
               LAST_VALUE(STSDAT||STS)
               OVER(PARTITION BY BRNCOD, CMPID
                    ORDER BY STSDAT||STS ASC
                    ROWS BETWEEN CURRENT ROW
                             AND UNBOUNDED FOLLOWING) MAX_DTSTS,
               AMT
          FROM CUSTOMER
         WHERE STSDAT <= '20031130') C
 WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
   AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
   AND C.AMT > 0;

분석함수 사용
- MAX (~) OVER (PARTITION BY ~)

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
               MAX(STSDAT||STS)
               OVER(PARTITION BY BRNCOD, CMPID) MAX_DTSTS,
              AMT
         FROM CUSTOMER
        WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
  AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
  AND C.AMT > 0;

분석함수 사용
- MAX (~) OVER (PARTITION BY ~) + WINDOWING 절 추가하기

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
               MAX(STSDAT||STS)
               OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT DESC,STS DESC
               ROW UNBOUNDED PRECEDING ) MAX_DTSTS,
              AMT
         FROM CUSTOMER
        WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
  AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
  AND C.AMT > 0;

SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
  FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
               MAX(STSDAT||STS)
               OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT ASC, STS ASC
               ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MAX_DTSTS,
              AMT
         FROM CUSTOMER
        WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
  AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
  AND C.AMT > 0;