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)| -----------------------------------------------------------