| {code:SQL | title= SQL 변환 | borderStyle=solid} -- 변환 전 select e.department_id, e.job_id, avg(e.salary) avg_sal from employees e group by grouping sets(e.department_id, e.job_id); |
===========
PLAN
===========
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | 1 | 31 | 00:00:00.01 | 46 | 3 | 3 | ||||
| 1 | TEMP TABLE TRANSFORMATION | 1 | 31 | 00:00:00.01 | 46 | 3 | 3 | ||||
| 2 | LOAD AS SELECT | 1 | 0 | 00:00:00.01 | 11 | 0 | 1 | 264K | 264K | 264K (0) | |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 | 0 | 0 | |||
| 4 | LOAD AS SELECT | 1 | 0 | 00:00:00.01 | 10 | 1 | 1 | 264K | 264K | 264K (0) | |
| 5 | HASH GROUP BY | 1 | 19 | 00:00:00.01 | 6 | 1 | 0 | 763K | 763K | 1272K (0) | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664F_4BB8158F | 1 | 107 | 00:00:00.01 | 6 | 1 | 0 | |||
| 7 | LOAD AS SELECT | 1 | 0 | 00:00:00.01 | 8 | 0 | 1 | 264K | 264K | 264K (0) | |
| 8 | HASH GROUP BY | 1 | 12 | 00:00:00.01 | 3 | 0 | 0 | 776K | 776K | 902K (0) | |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664F_4BB8158F | 1 | 107 | 00:00:00.01 | 3 | 0 | 0 | |||
| 10 | VIEW | 1 | 31 | 00:00:00.01 | 8 | 2 | 0 | ||||
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6650_4BB8158F | 1 | 31 | 00:00:00.01 | 8 | 2 | 0 |
-- 변환 후
select e.department_id, NULL job_id, avg(e.salary) avg_sal
from employees e
group by e.department_id
union all
select NULL department_id, e.job_id, avg(e.salary) avg_sal
from employees e
group by e.job_id;
===========
PLAN
===========
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | 1 | 31 | 00:00:00.01 | 14 | ||||
| 1 | UNION-ALL | 1 | 31 | 00:00:00.01 | 14 | ||||
| 2 | HASH GROUP BY | 1 | 12 | 00:00:00.01 | 7 | 776K | 776K | 1666K (0) | |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 | |||
| 4 | HASH GROUP BY | 1 | 19 | 00:00:00.01 | 7 | 763K | 763K | 1264K (0) | |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 |
-- 힌트적용 후
select /*+ expand_gset_to_union */
e.department_id, e.job_id, avg(e.salary) avg_sal
from employees e
group by grouping sets(e.department_id, e.job_id);
===========
PLAN
===========
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | 1 | 31 | 00:00:00.01 | 14 | ||||
| 1 | VIEW | 1 | 31 | 00:00:00.01 | 14 | ||||
| 2 | UNION-ALL | 1 | 31 | 00:00:00.01 | 14 | ||||
| 3 | HASH GROUP BY | 1 | 12 | 00:00:00.01 | 7 | 776K | 776K | 1665K (0) | |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 | |||
| 5 | HASH GROUP BY | 1 | 19 | 00:00:00.01 | 7 | 763K | 763K | 1263K (0) | |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 |
* 10053 트레이스로 확인
{code:SQL}
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature(): NULL
Registered qb: SEL$AE491F42 0xd3f6724 (SPLIT QUERY BLOCK FOR GSET-TO-UNION SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature(): NULL
Registered qb: SEL$AE491F43 0xd3f365c (SPLIT QUERY BLOCK FOR GSET-TO-UNION SEL$1; SEL$1)
---------------------
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"
중략..
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$21B49613 nbfros=1 flg=0
fro(0): flg=0 objn=73948 hint_alias="E"@"SEL$1"
Registered qb: SEL$AE491F43_1 0xd9dcf90 (GROUPING SET TO UNION SEL$AE491F43)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$AE491F43_1 nbfros=1 flg=0
fro(0): flg=0 objn=73948 hint_alias="E"@"SEL$1"
Registered qb: SEL$AE491F43_2 0xd9daf6c (GROUPING SET TO UNION SEL$AE491F43)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$AE491F43_2 nbfros=1 flg=0
fro(0): flg=0 objn=73948 hint_alias="E"@"SEL$1"
Registered qb: SET$D2F1856C 0xd9de4a8 (SET QUERY BLOCK SEL$AE491F43)