■ GROUPING SETS를 GROYP BY + UNION ALL(기본적으)로 변경시키는 기능을 말한다.

-- SQL을 살펴보자. 어떻하면 GROUPING SETS를 GROYP BY + UNION ALL(기본적으)로 변경시키느냐

SELECT DEPARTMENT_ID, JOB_ID, AVG (SALARY) AVG_SAL
  FROM EMPLOYEE
 GROUP BY GROUPING SETS (DEPARTMENT_ID, JOB_ID); 
 
-- GSTU 개념의 파라미터나 힌트를 쓰지않은 실행계획
-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |   107 |  3531 |    12  (17)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6CB9_EDE99 |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | EMPLOYEE                 |   107 |  1712 |     4   (0)| 00:00:01 |
|   4 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6CBA_EDE99 |       |       |            |          |
|   5 |    HASH GROUP BY           |                          |     1 |    20 |     3  (34)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6CB9_EDE99 |     1 |    20 |     2   (0)| 00:00:01 |
|   7 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6CBA_EDE99 |       |       |            |          |
|   8 |    HASH GROUP BY           |                          |     1 |    26 |     3  (34)| 00:00:01 |
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6CB9_EDE99 |     1 |    26 |     2   (0)| 00:00:01 |
|  10 |   VIEW                     |                          |     1 |    33 |     2   (0)| 00:00:01 |
|  11 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6CBA_EDE99 |     1 |    46 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

-- 위의 SQL을 논리적인 즉, GSTU 개념으로 풀어놓으면 다음과 같다

EXPLAIN PLAN FOR  
SELECT DEPARTMENT_ID, NULL AS JOB_ID, AVG (SALARY) AVG_SAL
  FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID
UNION ALL
SELECT NULL AS DEPARTMENT_ID, JOB_ID, AVG (SALARY) AVG_SAL
  FROM EMPLOYEE
 GROUP BY JOB_ID;   
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    30 |   324 |     9  (56)| 00:00:01 |
|   1 |  UNION-ALL                    |            |       |       |            |          |
|   2 |   HASH GROUP BY               |            |    11 |    77 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMPLOYEE   |   107 |   749 |     4   (0)| 00:00:01 |
|   4 |   SORT GROUP BY NOSORT        |            |    19 |   247 |     4   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |   107 |  1391 |     4   (0)| 00:00:01 |
|   6 |     INDEX FULL SCAN           | EMP_JOB_IX |   107 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

■ 제어 파라미터 와 힌트 사용

ALTER SESSION SET "_union_rewrite_for_gs" = FORCE_NO_COMBINE;

/*+ EXPAND_GSET_TO_UNION  */

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

SELECT /*+ EXPAND_GSET_TO_UNION  */
       DEPARTMENT_ID, JOB_ID, AVG (SALARY) AVG_SAL
  FROM EMPLOYEE
 GROUP BY GROUPING SETS (DEPARTMENT_ID, JOB_ID);

ALTER SESSION SET EVENTS '10053 trace name context off';

----------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |           |       |       |     9 |           |
| 1   |  VIEW                           |           |    30 |   990 |     9 |  00:00:01 |
| 2   |   UNION-ALL                     |           |       |       |       |           |
| 3   |    HASH GROUP BY                |           |    11 |    77 |     5 |  00:00:01 |
| 4   |     TABLE ACCESS FULL           | EMPLOYEE  |   107 |   749 |     4 |  00:00:01 |
| 5   |    SORT GROUP BY NOSORT         |           |    19 |   247 |     4 |  00:00:01 |
| 6   |     TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |   107 |  1391 |     4 |  00:00:01 |
| 7   |      INDEX FULL SCAN            | EMP_JOB_IX|   107 |       |     1 |  00:00:01 |
----------------------------------------------------+-----------------------------------+

 -- 앞선 UNION ALL 쿼리와 실행계획이 거의 흡사하다.

■ 트레이스를 살펴보자.

************************************
Cost-based predicate pushdown (JPPD)
************************************
...중간생략
Final query after transformations:******* UNPARSED QUERY IS *******

-- 최종적으로 아래와 같이 SQL 변환이 이뤄어졌다.
SELECT /*+ EXPAND_GSET_TO_UNION */
       "$kkqt_split_view"."DEPARTMENT_ID" "DEPARTMENT_ID",
       "$kkqt_split_view"."JOB_ID" "JOB_ID",
       "$kkqt_split_view"."VW_COL_1" "AVG_SAL"
FROM  (
      (SELECT /*+ EXPAND_GSET_TO_UNION */
              AVG("EMPLOYEE"."SALARY") "VW_COL_1",
              NULL "JOB_ID",
              "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID"
         FROM "TLO"."EMPLOYEE" "EMPLOYEE"
        GROUP BY "EMPLOYEE"."DEPARTMENT_ID")
      UNION ALL 
      (SELECT /*+ EXPAND_GSET_TO_UNION */
              AVG("EMPLOYEE"."SALARY") "VW_COL_1",
              "EMPLOYEE"."JOB_ID" "JOB_ID",
              NULL "DEPARTMENT_ID"
         FROM "TLO"."EMPLOYEE" "EMPLOYEE"
        GROUP BY "EMPLOYEE"."JOB_ID")
      ) "$kkqt_split_view"
     
kkoqbc: optimizing query block SEL$AE491F43_2 (#4)

-- 그렇다면 SQL변환이 이뤄지기전 무슨일이 발생된 것일까

Registered qb: SEL$AE491F43 0x3d3604 (SPLIT QUERY BLOCK FOR GSET-TO-UNION SEL$1; SEL$1)
--> 쿼리블럭 SEL$1에 쿼리블럭의 분리가 일어나고 새로운 쿼리블럭(SEL$AE491F43)이 생성되었다.
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$AE491F43 nbfros=1 flg=0
    fro(0): flg=5 objn=0 hint_alias="$kkqt_split_view"@"SEL$AE491F43"
--> 아직 완성된게 아니라 단순히 쿼리 블럭을 분리한 것에 불과하며
    추후 UNION을 적용하기 위해서 위아래로 배치작업이 남아있다.

--> 분리 > 생성 > 배치

---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$21B49613 nbfros=1 flg=0
    fro(0): flg=0 objn=86728 hint_alias="EMPLOYEE"@"SEL$1"

Registered qb: SEL$AE491F43_1 0x8fc1d8 (GROUPING SET TO UNION SEL$AE491F43)
--> 새로 생성된 쿼리블럭 SEL$AE491F43은 UNION을 적용하기 위해 분리

---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$AE491F43_1 nbfros=1 flg=0
    fro(0): flg=0 objn=86728 hint_alias="EMPLOYEE"@"SEL$1"

Registered qb: SEL$AE491F43_2 0x8fab40 (GROUPING SET TO UNION SEL$AE491F43)
--> 새로 생성된 쿼리블럭 SEL$AE491F43은 UNION을 적용하기 위해 분리

---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$AE491F43_2 nbfros=1 flg=0
    fro(0): flg=0 objn=86728 hint_alias="EMPLOYEE"@"SEL$1"

Registered qb: SET$D2F1856C 0x8fd8ec (SET QUERY BLOCK SEL$AE491F43)
--> 연이어 UNION의 위아래 통합하는 인라인뷰내의 전체쿼리블럭생성

---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SET$D2F1856C nbfros=1 flg=0
    fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$D2F1856C"

■ 제약사항
-- 1.ROWNUM을 사용하면 사용할수없다.
-- 2. EXISTS 나 NOT EXISTS 사용시 GSTU가 발생하면 SEMI/ANTI 조인이 발생하지 않는다.
      IN   이나 NOT IN     사용시 GSTU가 발생하면 SEMI/ANTI 조인이 발생한다.