[Oracle] 집계함수를 분석함수로 변경 튜닝

SELECT *
FROM   (SELECT /*+ LEADING(D) USE_NL(D EMP_V) PUSH_PRED(EMP_V) INDEX(EMP_V.E EMP_N1) */
               D.DNAME,
               EMP_V.DEPTNO,
               EMP_V.HIREDATE,
               EMP_V.MIN_JOB,
               ROWNUM CNT      
        FROM   (SELECT E.DEPTNO,
                       TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
                       MIN(E.JOB) MIN_JOB
                FROM   EMP E
                GROUP BY E.DEPTNO,
                         TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
               DEPT D
        WHERE  EMP_V.DEPTNO = D.DEPTNO
        AND    D.DNAME      = :B1  
        )
WHERE  CNT <= 15;

-- Trace 내용
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.000        0.004          0          0          0          0
Fetch        3   32.600       31.880          0      44245          0         15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5   32.600       31.886          0      44245          0         15

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   VIEW  (cr=44245 pr=0 pw=0 time=30636573 us)
    901    COUNT  (cr=44245 pr=0 pw=0 time=30639256 us)
    901     NESTED LOOPS  (cr=44245 pr=0 pw=0 time=30638349 us)
      1      TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=204 us)
    901      VIEW  (cr=44241 pr=0 pw=0 time=30637328 us)
1000007       SORT GROUP BY (cr=44241 pr=0 pw=0 time=31636297 us)
10000000        TABLE ACCESS FULL EMP (cr=44241 pr=0 pw=0 time=113 us)


-- 위에서 NESTED LOOPS이 되었음에도 불구하고 EMP_V 인라인 뷰 안의 EMP 테이블을 
-- 여전히 TABLE ACCESS FULL을 하고 있으며, 부분범위처리 또한 되지 않았다.
-- 그 이유를 확인하기 위해 아래에서 XPLAN의 Predicate Information 내용을 살펴보자.

-- XPLAN 내용
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   706K|    40M|       | 40734   (4)| 00:08:09 |
|*  1 |  VIEW                  |      |   706K|    40M|       | 40734   (4)| 00:08:09 |
|   2 |   COUNT                |      |       |       |       |            |          |
|   3 |    NESTED LOOPS        |      |   706K|    15M|       | 40734   (4)| 00:08:09 |
|*  4 |     TABLE ACCESS FULL  | DEPT |     1 |    11 |       |     3   (0)| 00:00:01 |
|*  5 |     VIEW               |      |   706K|  8284K|       | 40731   (4)| 00:08:09 |
|   6 |      SORT GROUP BY     |      |  3534K|    43M|   311M| 40731   (4)| 00:08:09 |
|   7 |       TABLE ACCESS FULL| EMP  |    10M|   124M|       | 12240   (3)| 00:02:27 |
---------------------------------------------------------------------------------------
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - filter("CNT"<=15)                                                               
   4 - filter("D"."DNAME"=:B1)                                                         
   5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") 


-- Id 5번을 보면 HASH에서 NL로 변경되었음에도 불구하고 5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") 
-- 내용처럼 조인키가 Merge가 되지 않았다. 왜 NL 조인으로 변경하였음에도 불구하고 조인키가 
-- Merge가 되지 않았을까? 그 이유는 바로 EMP_V 인라인뷰에 그룹함수가 있기 때문이다.
-- 일반적으로 View Merging이 되기 위해서는 아래의 제약조건이 없어야 하는데 이를 일부 살펴보면 
-- 다음과 같다.

① 집합 연산자를 사용할 경우(union, intersect, minus)
② connect by 절을 사용할 경우
③ rownum을 사용할 경우
④ 그룹함수를 사용할 경우(avg, count, max, min, sum)
⑤ 분석함수를 사용할 경우(row_number, rank, ...)

-- 이 중 여기서의 문제는 4번째 Case인 그룹함수를 EMP_V에서 사용했기 때문에 Merge가 일어나지 못하고 
-- 있는데, 일반적으로 위의 구문을 사용할경우 옵티마이저는 가급적 해당 집합을 먼저 구성한 다음 
-- 조인을 해야 데이터 정합성을 해치지 않기 때문에 View Merging을 가급적 시도하지 않는다.

-- 하지만 이런 그룹함수들도 분석함수를 사용할 경우 Complex View Merging을 통해 옵티마이저가 
-- Merging을 시키도록 할 수 있다. 

 

  여기서는 그룹함수의 MIN을 분석함수의 ROW_NUMBER를 통해 변경하는 방법을 살펴보려고 한다. 변경내용에만 집중하기 위해 EMP_V 인라인 뷰 내용만 가지고 설명을 하고자 한다.

 
-- 1. EMP_V 인라인 뷰 그룹함수 버전
SELECT E.DEPTNO,
       TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
       MIN(E.JOB) MIN_JOB
FROM   EMP E
GROUP  BY E.DEPTNO,
          TO_CHAR(E.HIREDATE, 'YYYYMMDD');

-- 위의 내용을 보면 DEPTNO, HIREDATE 2개의 컬럼을 Grouping 지은 후, 
-- 그 중 JOB 컬럼 값 중 최소값(MIN)을 가져오는 내용이다.
-- 이를 분석함수로 대체할 경우 어떻게 하면 될까? 바로 ROW_NUMBER() 함수를 
-- 사용하여 JOB 컬럼값을 Asc로 ORDER BY 시킨 후 첫 번째 값만 가져오도록 변경해주면 된다.

-- 2. EMP_V 인라인 뷰 분석함수 버전
SELECT INLINE_EMP.DEPTNO,
       INLINE_EMP.HIREDATE,
       INLINE_EMP.MIN_JOB
FROM   (SELECT E.DEPTNO,
               TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
               E.JOB MIN_JOB,
               ROW_NUMBER() OVER(PARTITION BY E.DEPTNO, TO_CHAR(E.HIREDATE, 'YYYYMMDD') ORDER BY E.JOB) GUBN
        FROM   EMP E) INLINE_EMP
WHERE  INLINE_EMP.GUBN = 1;


-- 즉, 위의 내용처럼 PARTITION BY를 통해 2개의 컬럼을 Grouping 시킨 후 ORDER BY 한 결과를 
-- 밖에서 1 값만 취득하면 그룹함수의 MIN()과 동일한 효과를 나타낼 수 있으며,
-- 이를 통해 EMP_V 인라인 뷰 안으로 조인키인 DEPTNO 값이 Merge 되도록 할 수 있다.  
    

 

출처: https://argolee.tistory.com/62 [놀멍:티스토리]