Oracle 21c에 UPDATE 문의 SET 절에 사용한 서브쿼리가 Unnesting되는 쿼리 변환이 추가되었습니다. 참고로 이 기능은 New Features Guide에 포함되어 있지 않습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 (c1, c2, c3, c4) AS SELECT ROWNUM, ROWNUM, ROWNUM, ROWNUM FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t2 (c1, c2) AS SELECT c1, c2 FROM t1;
CREATE TABLE t3 (c1, c2) AS SELECT * FROM t2;
CREATE INDEX t2_x1 ON t2 (c1);
아래는 19.3 버전의 실행 계획입니다. UPDATE 문의 SET 절에 사용한 서브쿼리가 스칼라 서브쿼리로 처리됩니다. t3 테이블에 인덱스가 없어 테이블을 100번 풀 스캔합니다.
-- 2: 19.3
UPDATE t1 a
SET c2 = (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c2)
, c3 = (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2)
WHERE a.c1 <= 100;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 0 |00:00:02.16 | 211K|
| 1 | UPDATE | T1 | 1 | 0 |00:00:02.16 | 211K|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 100 |00:00:00.02 | 3461 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 100 | 100 |00:00:00.01 | 210 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 100 | 100 |00:00:00.01 | 110 |
|* 5 | TABLE ACCESS FULL | T3 | 100 | 100 |00:00:02.14 | 207K| -- !
-----------------------------------------------------------------------------------------------
아래는 21.3 버전의 실행 계획입니다. UPDATE 문의 SET 절에 사용한 서브쿼리가 Unnesting된 것을 확인할 수 있습니다. 2-1번 UPDATE 문은 t2 테이블을 NL 조인, t3 테이블을 해시 조인합니다. 2-2번 UPDATE 문은 WHERE 절을 삭제하여 t2, t3 테이블을 모두 해시 조인합니다.
-- 3-1: 21.3
UPDATE t1 a
SET c2 = (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c2)
, c3 = (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2)
WHERE a.c1 <= 100;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 0 |00:00:00.24 | 5654 |
| 1 | UPDATE | T1 | 1 | 0 |00:00:00.24 | 5654 |
| 2 | NESTED LOOPS OUTER | | 1 | 100 |00:00:00.24 | 5552 |
|* 3 | HASH JOIN OUTER | | 1 | 100 |00:00:00.24 | 5538 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 100 |00:00:00.02 | 3461 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 1000K|00:00:00.08 | 2077 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 100 | 100 |00:00:00.01 | 14 |
|* 7 | INDEX RANGE SCAN | T2_X1 | 100 | 100 |00:00:00.01 | 13 |
------------------------------------------------------------------------------------------------
-- 3-2: 21.3
UPDATE t1 a
SET c2 = (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c2)
, c3 = (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2);
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | Writes |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 0 |00:00:07.46 | 1035K| 13616 | 13609 |
| 1 | UPDATE | T1 | 1 | 0 |00:00:07.46 | 1035K| 13616 | 13609 |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 1000K|00:00:02.90 | 7730 | 13610 | 13609 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 1000K|00:00:00.02 | 2077 | 0 | 0 |
|* 4 | HASH JOIN OUTER | | 1 | 1000K|00:00:00.94 | 5568 | 5674 | 5952 |
| 5 | TABLE ACCESS FULL | T1 | 1 | 1000K|00:00:00.03 | 3461 | 0 | 0 |
| 6 | TABLE ACCESS FULL | T3 | 1 | 1000K|00:00:00.07 | 2077 | 0 | 0 |
--------------------------------------------------------------------------------------------------
UPDATE 문의 SET 절에 사용한 서브쿼리의 Unnesting도 UNNEST, NO_UNNEST 힌트로 제어할 수 있습니다. 아래 UPDATE 문은 상단 서브쿼리에 NO_UNNEST 힌트를 사용합니다. 해당 서브쿼리가 Filter 방식으로 처리된 것을 확인할 수 있습니다.
-- 4
UPDATE t1 a
SET c2 = (SELECT /*+ NO_UNNEST */ x.c2 FROM t2 x WHERE x.c1 = a.c2)
, c3 = (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1000K| 5003K (20)|
| 1 | UPDATE | T1 | | |
|* 2 | HASH JOIN OUTER | | 1000K| 3082 (1)|
| 3 | TABLE ACCESS FULL | T1 | 1000K| 617 (1)|
| 4 | TABLE ACCESS FULL | T3 | 1000K| 373 (2)|
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 4 (0)| -- !
|* 6 | INDEX RANGE SCAN | T2_X1 | 1 | 3 (0)| -- !
---------------------------------------------------------------------------
이 기능은 _optimizer_unnest_update_set_subq 파라미터 및 30681521 Fix Control과 관련이 있습니다.
-- 5-1
NAME VALUE DEFAULT_VALUE DESCRIPTION
--------------------------------- ----- ------------- -----------------------------------------------
_optimizer_unnest_update_set_subq on on enables unnesting of subquery in set for update
-- 5-2
NAME ORDINAL VALUE ISDEFAULT
--------------------------------- ------- ---------- ---------
_optimizer_unnest_update_set_subq 1 ON FALSE
_optimizer_unnest_update_set_subq 2 OFF FALSE
_optimizer_unnest_update_set_subq 3 CONSTRAINT FALSE
-- 5-3
SELECT bugno, value, sql_feature, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 30681521;
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
-------- ----- --------------------- ---------------------------------------------------------------- ------------------------
30681521 1 QKSFM_UNNEST_30681521 enable unnesting of subqueries in set clause of update statement 21.1.0
1개의 행이 선택되었습니다.
현재 _optimizer_unnest_update_set_subq 파라미터는 동작하지 않은 것으로 보이며 30681521 Fix Control로 기능을 제어할 수 있습니다.
-- 6-1: 21.3
ALTER SESSION SET "_fix_control" = '30681521:0';
-- 6-2: 21.3
UPDATE t1 a
SET c2 = (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c2)
, c3 = (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2);
--------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 287K|
| 1 | UPDATE | T1 | |
| 2 | TABLE ACCESS FULL | T1 | 287K|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1727 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 691 |
|* 5 | TABLE ACCESS FULL | T3 | 1727 |
--------------------------------------------------------------