■ 개념 - GROUPING SETS를 ROLLUP으로 변환하라

-- 앞서 GSTU 에서는 기본적으로 UNION ALL로 변환된다.
-- 하지만 무거운 연산 SELECT + GROUP BY를 2번씩 수행하게 되는 비효율 때문에 GROYPING SETS를
   ROLLUP으로 변경이 가능한 경우를 만나면 이를 변환시킨다.

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT DEPARTMENT_ID, JOB_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY GROUPING SETS ((DEPARTMENT_ID, JOB_ID), (DEPARTMENT_ID));
 
ALTER SESSION SET EVENTS '10053 trace name context off'; 

----------------------------------------+-----------------------------------+
| Id  | Operation             | Name    | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |         |       |       |     5 |           |
| 1   |  SORT GROUP BY ROLLUP |         |   107 |  1712 |     5 |  00:00:01 |
| 2   |   TABLE ACCESS FULL   | EMPLOYEE|   107 |  1712 |     4 |  00:00:01 |
----------------------------------------+-----------------------------------+

-- 저자처럼.. 뭔가 이상하다! 위의 플랜을 보면 GROUPING SETS가 ROLLUP으로 바뀌어 버렸다.

■ GSTR의 논리적 SQL 변환

EXPLAIN PLAN FOR  
SELECT DEPARTMENT_ID, JOB_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID, JOB_ID
UNION ALL
SELECT DEPARTMENT_ID, NULL AS JOB_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID;
      
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   118 |  1789 |    10  (60)| 00:00:01 |
|   1 |  UNION-ALL          |          |       |       |            |          |
|   2 |   HASH GROUP BY     |          |   107 |  1712 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEE |   107 |  1712 |     4   (0)| 00:00:01 |
|   4 |   HASH GROUP BY     |          |    11 |    77 |     5  (20)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMPLOYEE |   107 |   749 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

-- 위의 SQL을 보면 앞선 챕터에서 보았던 (PARTIAL) ROLLUP과 개념이 같다라는것이 머리속에서 번쩍!

■ 또 변환해보자.

EXPLAIN PLAN FOR  
SELECT DEPARTMENT_ID, JOB_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID, ROLLUP (JOB_ID);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   107 |  1712 |     5  (20)| 00:00:01 |
|   1 |  SORT GROUP BY ROLLUP|          |   107 |  1712 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMPLOYEE |   107 |  1712 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------

■ GROUPING SETS를 ROLLUP으로 바꿔어야 하는 이유

-- ROLLUP       이 집합을 단 한번만 액세스하므로 우월한 성능을 갖추고 있다.
-- GROUPING SETS는 집합을 2번       액세스하므로 ROLLUP 보다 느릴수 밖에 없는것이다.
-- 첫째도 성능 둘째도 성능 셋째도 성능

■ (PARTIAL) ROLLUP 이라고 한 이유

-- ROLLUP (DEPARTMENT_ID, JOB_ID) 으로 사용하지않고
-- DEPARTMENT_ID 컬럼이 ROLLUP에서 빠져나와 GROUP BY 절로 들어갔기 때문이다.

■ PARTIAL ROLLUP을 또또 논리적으로 SQL을 변환해보자.

SELECT A, B, C, SUM(D)
FROM   T
GROUP  BY C, ROLLUP(A, B)


GROUP BY C, A, B
UNION ALL
GROUP BY C, A
UNION ALL
GROUP BY C, NULL

-- 이젠 익숙한 논리적 SQL 이다.
-- 저기다 상수 조건 추가하면 WHERE JOB_ID = 'MK_REP' 이것도 익숙한 논리적 SQL다.
-- (저자처럼..) 그렇다면 머리속에서 번쩍 떠올려야 한다. ROLLUP 과 CUBE가 나올것이다.

■ 또또또 ROLLUP, CUBE로 SQL을 변환해보자. (개인적 호기심)

EXPLAIN PLAN FOR  
SELECT * FROM (
SELECT DEPARTMENT_ID, JOB_ID, MANAGER_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID, ROLLUP(MANAGER_ID, JOB_ID)
)
 WHERE JOB_ID = 'MK_REP';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    20 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY               |            |     1 |    20 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    20 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"='MK_REP')


EXPLAIN PLAN FOR  
SELECT * FROM (
SELECT DEPARTMENT_ID, JOB_ID, MANAGER_ID, SUM (SALARY) AS SUM_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID, CUBE(MANAGER_ID, JOB_ID)
)
 WHERE JOB_ID = 'MK_REP';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    46 |     3  (34)| 00:00:01 |
|   1 |  VIEW                         |            |     1 |    46 |     3  (34)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP        |            |     1 |    20 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    20 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("JOB_ID"='MK_REP')

The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 11일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^