Oracle 23c에 UNION ALL 연산자를 포함한 인라인 뷰에 GROUP BY 절을 Pushing하는 쿼리 변환이 추가되었습니다. 참고로 이 기능은 New Features Guide에 포함되어 있지 않습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT CEIL (ROWNUM / 100), 1 FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t2 (c1, c2) AS SELECT * FROM t1;
아래는 19.3 버전의 실행 계획입니다. UNION ALL 연산을 수행한 후 집계를 수행합니다.
-- 2: 19.3
SELECT c1, SUM (c2)
FROM (SELECT * FROM t1
UNION ALL
SELECT * FROM t2)
GROUP BY c1;
--------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 |00:00:00.70 | 3300 | |
| 1 | HASH GROUP BY | | 1000 |00:00:00.70 | 3300 | 1400K (0)|
| 2 | VIEW | | 2000K|00:00:00.54 | 3300 | |
| 3 | UNION-ALL | | 2000K|00:00:00.39 | 3300 | |
| 4 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.05 | 1650 | |
| 5 | TABLE ACCESS FULL| T2 | 1000K|00:00:00.05 | 1650 | |
--------------------------------------------------------------------------------
아래는 23.2 버전의 실행 계획입니다. UNION ALL 대상을 각각 집계한 후 최종 집계를 수행하며, Outline Data 항목에 PUSH_GBY_INTO_UNION_ALL 힌트가 표시됩니다. 먼저 그룹핑을 수행함으로써 최종 그룹핑 대상을 줄여 성능을 향상시키는 방식입니다.
-- 2
SELECT c1, SUM (c2)
FROM (SELECT * FROM t1
UNION ALL
SELECT * FROM t2)
GROUP BY c1;
---------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 |00:00:00.14 | 3300 | |
| 1 | HASH GROUP BY | | 1000 |00:00:00.14 | 3300 | 1527K (0)|
| 2 | VIEW | | 2000 |00:00:00.14 | 3300 | |
| 3 | UNION-ALL | | 2000 |00:00:00.14 | 3300 | |
| 4 | HASH GROUP BY | | 1000 |00:00:00.06 | 1650 | 1546K (0)|
| 5 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.02 | 1650 | |
| 6 | HASH GROUP BY | | 1000 |00:00:00.07 | 1650 | 1546K (0)|
| 7 | TABLE ACCESS FULL| T2 | 1000K|00:00:00.01 | 1650 | |
---------------------------------------------------------------------------------
Outline Data
-------------
PUSH_GBY_INTO_UNION_ALL(@"SEL$3")
PUSH_GBY_INTO_UNION_ALL(@"SEL$2")
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL]
SEL$2 (PARSER)
SEL$FC1F66D1 (GROUP-BY PUSHDOWN INTO UNION-ALL BRANCH SEL$2) [FINAL]
SEL$3 (PARSER)
SEL$81C5BDA6 (GROUP-BY PUSHDOWN INTO UNION-ALL BRANCH SEL$3) [FINAL]
SET$1 (PARSER)
SET$A91B66D7 (GROUP-BY PUSHDOWN INTO UNION-ALL VIEW SET$1 ;
SEL$2,SEL$3) [FINAL]
PUSH_GBY_INTO_UNION_ALL 힌트는 23.1 버전에 추가되었으며 INVERSE 힌트는 NO_PUSH_GBY_INTO_UNION_ALL 힌트입니다.
-- 3
SELECT name, inverse, target_level, version
FROM v$sql_hint
WHERE name = 'PUSH_GBY_INTO_UNION_ALL';
NAME INVERSE TARGET_LEVEL VERSION
----------------------- -------------------------- ------------ -------
PUSH_GBY_INTO_UNION_ALL NO_PUSH_GBY_INTO_UNION_ALL 2 23.1.0
1개의 행이 선택되었습니다.
인라인 뷰의 상단 쿼리에 NO_PUSH_GBY_INTO_UNION_ALL 힌트를 사용하면 해당 쿼리 블록에 GROUP BY 절이 Pushing되지 않은 것을 확인할 수 있습니다.
-- 4
SELECT c1, SUM (c2)
FROM (SELECT /*+ NO_PUSH_GBY_INTO_UNION_ALL */ * FROM t1
UNION ALL
SELECT * FROM t2)
GROUP BY c1;
-----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 652 (11)|
| 1 | HASH GROUP BY | | 1000 | 652 (11)|
| 2 | VIEW | | 1001K| 624 (7)|
| 3 | UNION-ALL | | 1001K| 624 (7)|
| 4 | TABLE ACCESS FULL | T1 | 1000K| 298 (2)|
| 5 | HASH GROUP BY | | 1000 | 326 (11)|
| 6 | TABLE ACCESS FULL| T2 | 1000K| 298 (2)|
-----------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
4 - SEL$2
- NO_PUSH_GBY_INTO_UNION_ALL
참고로 해당 기능은 _optimizer_push_gby_into_union_all 파라미터와 관련이 있습니다.
-- 5
NUM NAME VALUE DEFAULT_VALUE DESCRIPTION
---- ---------------------------------- ----- ------------- ------------------------------------------------------
5249 _optimizer_push_gby_into_union_all TRUE TRUE consider pushing down group-by into union-all branches
아래와 같이 _optimizer_push_gby_into_union_all 파라미터를 FALSE로 설정하면 기능이 동작하지 않는 것을 확인할 수 있습니다. 참고로 PUSH_GBY_INTO_UNION_ALL 힌트를 사용하면 기능이 동작하므로 힌트가 파라미터를 오버라이드하는 것으로 보입니다.
-- 6-1
ALTER SESSION SET "_optimizer_push_gby_into_union_all" = FALSE;
-- 6-2
SELECT c1, SUM (c2)
FROM (SELECT * FROM t1 UNION ALL
SELECT * FROM t2)
GROUP BY c1;
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 653 (11)|
| 1 | HASH GROUP BY | | 1000 | 653 (11)|
| 2 | VIEW | | 2000K| 597 (2)|
| 3 | UNION-ALL | | 2000K| 597 (2)|
| 4 | TABLE ACCESS FULL| T1 | 1000K| 298 (2)|
| 5 | TABLE ACCESS FULL| T2 | 1000K| 298 (2)|
----------------------------------------------------------
-- 6-3
SELECT c1, SUM (c2)
FROM (SELECT /*+ PUSH_GBY_INTO_UNION_ALL */ * FROM t1 UNION ALL
SELECT * FROM t2)
GROUP BY c1;
-----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 652 (11)|
| 1 | HASH GROUP BY | | 1000 | 652 (11)|
| 2 | VIEW | | 1001K| 624 (7)|
| 3 | UNION-ALL | | 1001K| 624 (7)|
| 4 | HASH GROUP BY | | 1000 | 326 (11)|
| 5 | TABLE ACCESS FULL| T1 | 1000K| 298 (2)|
| 6 | TABLE ACCESS FULL | T2 | 1000K| 298 (2)|
-----------------------------------------------------------