UNION ALL 을 GROUPING SETS 로 변환
UNION ALL 을 GROUPING SETS 로 변환
같은 테이블에 대해 여러 그룹화 수준 (예: 월별 + 일별) 을 UNION ALL 로 표현하면 같은 테이블을 N 번 풀 스캔합니다.
GROUPING SETS로 묶으면 옵티마이저가 TEMP TABLE TRANSFORMATION 으로 1 번 스캔 결과를 재사용하여 Cost 절감의 이점을 발생시킬 수 있습니다.
핵심 정리
- 변환의 목적: UNION ALL 로 같은 소스 테이블을 N 번 집계하던 패턴을
GROUPING SETS한 번으로 묶어 테이블 풀 스캔 N 회 → 1 회 로 줄입니다. - 수행 메커니즘: 옵티마이저가
TEMP TABLE TRANSFORMATION을 적용하여 join 결과를 임시 테이블에 한 번만 만들고, 각 그룹화 수준에서 이 temp 테이블을 재사용합니다. - 적용 효과 조건: 그룹화 대상 소스 (table / join 결과) 가 각 분기에서 동일 해야 합니다. 분기마다 다른 필터나 조인이 있다면 GROUPING SETS 로 바로 합치기 어렵습니다.
시나리오
T_SALE_LIST (대용량 fact, 약 932K 건) 와 T_CUST (마스터, 70K 건) 를 join 한 결과를 월별 + 일별 두 단위로 집계하는 쿼리입니다. 같은 데이터에 대해 두 그룹화 수준의 결과를 한 번에 보여주는 OLAP 패턴.
| 테이블 | 건수 | 인덱스 |
|---|---|---|
T_SALE_LIST | 약 932K | (full scan, SALE_DT filter) |
T_CUST | 70K | PK_T_CUST, IX_T_CUST_TY |
원본은 UNION ALL 로 표현되어 옵티마이저가 두 분기를 독립 실행하므로 같은 테이블이 두 번 풀 스캔됩니다.
1) 원본 — UNION ALL (테이블 2 회 풀 스캔)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT '월별' AS GUBUN,
TO_CHAR(S.SALE_DT,'YYYYMM') AS SALE_DT,
C.CUST_TY AS CUST_TYPE,
SUM(S.SALE_QY) AS SQLE_QY_SUM
FROM TEST.T_SALE_LIST S, ADMIN.T_CUST C
WHERE S.SALE_DT BETWEEN TO_DATE('2022/07/01','YYYY/MM/DD')
AND TO_DATE('2022/09/30','YYYY/MM/DD')
AND S.CUST_ID = C.CUST_ID
GROUP BY TO_CHAR(S.SALE_DT, 'YYYYMM'), C.CUST_TY
UNION ALL
SELECT '일별' AS GUBUN,
TO_CHAR(S.SALE_DT,'YYYYMMDD') AS SALE_DT,
C.CUST_TY AS CUST_TYPE,
SUM(S.SALE_QY) AS SQLE_QY_SUM
FROM TEST.T_SALE_LIST S, ADMIN.T_CUST C
WHERE S.SALE_DT BETWEEN TO_DATE('2022/07/01','YYYY/MM/DD')
AND TO_DATE('2022/09/30','YYYY/MM/DD')
AND S.CUST_ID = C.CUST_ID
GROUP BY TO_CHAR(S.SALE_DT, 'YYYYMMDD'), C.CUST_TY
ORDER BY 1,2,3;
옵티마이저는 두 분기를 독립 실행. T_SALE_LIST 풀 스캔이 두 번 발생합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 528 | 15840 | | 47161 (1) | 00:00:02 |
| 1 | SORT ORDER BY | | 528 | 15840 | | 47160 (1) | 00:00:02 |
| 2 | UNION-ALL | | | | | | |
| 3 | HASH GROUP BY | | 264 | 7920 | | 23580 (1) | 00:00:01 |
|* 4 | HASH JOIN | | 932K | 26M | 1576K | 23556 (1) | 00:00:01 |
| 5 | VIEW | index$_join$_002 | 70K | 751K | | 542 (1) | 00:00:01 |
|* 6 | HASH JOIN | | | | | | |
| 7 | INDEX FAST FULL SCAN| IX_T_CUST_TY | 70K | 751K | | 214 (0) | 00:00:01 |
| 8 | INDEX FAST FULL SCAN| PK_T_CUST | 70K | 751K | | 233 (0) | 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_SALE_LIST | 932K | 16M | | 21566 (1) | 00:00:01 |
| 10 | HASH GROUP BY | | 264 | 7920 | | 23580 (1) | 00:00:01 |
|*11 | HASH JOIN | | 932K | 26M | 1576K | 23556 (1) | 00:00:01 |
| 12 | VIEW | index$_join$_004 | 70K | 751K | | 542 (1) | 00:00:01 |
|*13 | HASH JOIN | | | | | | |
| 14 | INDEX FAST FULL SCAN| IX_T_CUST_TY | 70K | 751K | | 214 (0) | 00:00:01 |
| 15 | INDEX FAST FULL SCAN| PK_T_CUST | 70K | 751K | | 233 (0) | 00:00:01 |
|*16 | TABLE ACCESS FULL | T_SALE_LIST | 932K | 16M | | 21566 (1) | 00:00:01 |
--------------------------------------------------------------------------------------------------------
Id 9 와 Id 16 의 T_SALE_LIST 풀 스캔이 각각 Cost 21,566 으로, 같은 데이터를 두 번 읽는 명백한 중복입니다. 전체 Cost 47,161 의 92% 가 두 번의 풀 스캔에 소비됩니다.
2) 수정 — GROUPING SETS (TEMP TABLE 1 회 풀 스캔 + 재사용)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT CASE WHEN SALE_DT1 IS NOT NULL THEN '월별' ELSE '일별' END AS GUBUN,
CASE WHEN SALE_DT1 IS NOT NULL THEN SALE_DT1 ELSE SALE_DT2 END AS SALE_DT,
CUST_TYPE,
SQLE_QY_SUM
FROM (SELECT TO_CHAR(S.SALE_DT, 'YYYYMM') AS SALE_DT1,
TO_CHAR(S.SALE_DT, 'YYYYMMDD') AS SALE_DT2,
C.CUST_TY AS CUST_TYPE,
SUM(S.SALE_QY) AS SQLE_QY_SUM
FROM TEST.T_SALE_LIST S, ADMIN.T_CUST C
WHERE SALE_DT BETWEEN TO_DATE('2022/07/01','YYYY/MM/DD')
AND TO_DATE('2022/09/30','YYYY/MM/DD')
AND S.CUST_ID = C.CUST_ID
GROUP BY GROUPING SETS ((TO_CHAR(S.SALE_DT, 'YYYYMM'), C.CUST_TY),
(TO_CHAR(S.SALE_DT, 'YYYYMMDD'), C.CUST_TY)))
ORDER BY 1, 2, 3;
GROUPING SETS 로 두 그룹화 수준을 한 번에 표현합니다. 옵티마이저는 join 결과를 임시 테이블에 한 번만 만들고 각 그룹화 수준에서 이 temp 를 재사용합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 264 | 6864 | | 25679 (1) | 00:00:02 |
| 1 | SORT ORDER BY | | 264 | 6864 | | 25679 (1) | 00:00:02 |
| 2 | VIEW | | 264 | 6864 | | 25678 (1) | 00:00:02 |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660E_725B8 | | | | | |
|* 5 | HASH JOIN | | 932K | 26M | 1576K | 23556 (1) | 00:00:01 |
| 6 | VIEW | index$_join$_005 | 70K | 751K | | 542 (1) | 00:00:01 |
|* 7 | HASH JOIN | | | | | | |
| 8 | INDEX FAST FULL SCAN | IX_T_CUST_TY | 70K | 751K | | 214 (0) | 00:00:01 |
| 9 | INDEX FAST FULL SCAN | PK_T_CUST | 70K | 751K | | 233 (0) | 00:00:01 |
|*10 | TABLE ACCESS FULL | T_SALE_LIST | 932K | 16M | | 21566 (1) | 00:00:01 |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660F_725B8 | | | | | |
| 12 | HASH GROUP BY | | 3 | 24 | | 1060 (3) | 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_725B8 | 932K | 7285K | | 1036 (1) | 00:00:01 |
| 14 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660F_725B8 | | | | | |
| 15 | HASH GROUP BY | | 3 | 27 | | 1060 (3) | 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_725B8 | 932K | 8195K | | 1036 (1) | 00:00:01 |
| 17 | VIEW | | 3 | 78 | | 2 (0) | 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_725B8 | 3 | 78 | | 2 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------
Id 3의TEMP TABLE TRANSFORMATION이 본 변환의 signature 입니다.Id 4~Id 10의 첫 번째 LOAD AS SELECT 가T_SALE_LIST × T_CUSThash join 결과를SYS_TEMP_0FD9D660E_725B8에 저장합니다.Id 11~Id 13의 두 번째 LOAD 가 temp 테이블에서 월별 그룹화를 수행합니다.Id 14~Id 16의 세 번째 LOAD 가 같은 temp 에서 일별 그룹화를 수행합니다.T_SALE_LIST풀 스캔이 1 번만 발생 (Id 10).- 전체 Cost
47,161 → 25,679으로 약 45% 절감.
분석
GROUPING SETS 의 의미
GROUPING SETS 는 SQL 표준 (Oracle 9i+) 으로, 한 쿼리에서 여러 그룹화 수준을 동시에 표현합니다.
1
2
3
4
5
GROUP BY GROUPING SETS (
(col_a, col_b), -- 첫 번째 그룹화 수준
(col_a), -- 두 번째 그룹화 수준
() -- 전체 합계
)
각 set 마다 별도 GROUP BY 결과가 생성되어 UNION ALL 처럼 합쳐집니다. 결과 row 수는 모든 set 의 GROUP BY 결과의 합.
ROLLUP / CUBE 도 GROUPING SETS 의 축약 표기입니다.
| 표현 | 동등한 GROUPING SETS |
|---|---|
ROLLUP(a, b, c) | ((a,b,c), (a,b), (a), ()) |
CUBE(a, b) | ((a,b), (a), (b), ()) |
TEMP TABLE TRANSFORMATION 의 동작
옵티마이저가 같은 source 결과를 여러 그룹화에 재사용 할 수 있다고 판단하면 자동으로 temp table 을 생성합니다.
- 첫 LOAD: source (join 결과 또는 풀 스캔 결과) 를
SYS_TEMP_$xxx에 저장 - 이후 LOAD: 같은 temp 에서 각 그룹화 수준을 read 후 결과를 다른 temp 에 저장
- 최종 VIEW: 모든 그룹화 결과 union 하여 반환
본 케이스에서 Id 4 ~ 10 이 source 작성, Id 11 ~ 13 이 월별 그룹화, Id 14 ~ 16 이 일별 그룹화. 같은 source temp (SYS_TEMP_0FD9D660E_725B8) 가 두 번 read 되지만 원본 테이블 스캔은 한 번만.
GROUP BY vs GROUPING SETS — 언제 무엇을 쓸까
| 상황 | 권장 |
|---|---|
| 단일 그룹화 수준 | GROUP BY |
| 데이터 볼륨이 작거나 인덱스가 정렬을 도와줌 | GROUP BY |
| 다중 그룹화 수준 (월/일, 부서/사원, 지역/도시) | GROUPING SETS |
| 같은 source 를 여러 번 스캔하던 UNION ALL 패턴 | GROUPING SETS 또는 ROLLUP / CUBE |
| 그룹화 컬럼 카디널리티가 매우 높아 정렬 / 해시 비용이 클 때 | GROUP BY 분리 검토 |
분기마다 필터가 다르면 어떻게 할까
GROUPING SETS 는 source 가 같을 때만 효과적입니다. 분기마다 WHERE 조건이 다르면 GROUPING SETS 로 바로 묶을 수 없으므로, 다음 패턴을 검토해 볼 수 있습니다.
CASE WHEN ... THEN ... END으로 컬럼을 가공한 뒤 단일 GROUP BY- 분기를 별도 inline view 로 둔 뒤 UNION ALL (이때는 Join Factorization 으로 자동 변환될 수 있음)
UNION ALL + 같은 테이블 + GROUP BY 패턴이 보이면 GROUPING SETS / ROLLUP / CUBE 로 변환을 시도해 볼 수 있습니다. 옵티마이저가 자동으로 TEMP TABLE TRANSFORMATION 을 적용해 테이블 풀 스캔 N 회를 1 회로 줄여 줍니다.
Cost 비교
| 단계 | 원본 | 수정 |
|---|---|---|
T_SALE_LIST 풀 스캔 회수 | 2 회 (각 21,566) | 1 회 (21,566) |
T_CUST 인덱스 join 회수 | 2 회 (각 542) | 1 회 (542) |
| 그룹화 수행 | 2 회 (UNION ALL 분기) | 2 회 (TEMP scan 후) |
| 합계 Cost | 47,161 | 25,679 |
각 그룹화 수행 비용은 동일하지만 source 스캔이 한 번으로 줄어 전체 Cost 가 절반에 가까워집니다.
정리
- UNION ALL + 같은 source + 다중 GROUP BY 패턴 은
GROUPING SETS로 묶으면 옵티마이저가 자동으로 TEMP TABLE TRANSFORMATION 을 적용해 테이블 풀 스캔 횟수를 줄입니다. - plan signature 는
TEMP TABLE TRANSFORMATION+LOAD AS SELECT (CURSOR DURATION MEMORY)+SYS_TEMP_$xxx이름의 temp 테이블입니다. - 선택 가이드: 단일 그룹화는
GROUP BY, 다중 그룹화 + 같은 source 는GROUPING SETS/ROLLUP/CUBE. 분기마다 필터가 다르면 GROUPING SETS 가 적용 안 되니 다른 변환 (CASE WHEN, JF 등) 검토.
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.