19c에서 INSERT 문이나 CTAS 문에 OR 조인 조건이 있는 아우터 조인을 사용하면 비효율적인 실행 계획으로 인해 성능이 저하될 수 있습니다.
테스트를 위해 아래의 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE TABLE t3 (c1 NUMBER, c2 NUMBER);
아래 쿼리는 OR 조인 조건을 사용했지만 b.c1 = a.c1 조건으로 해시 조인으로 아우터 조인을 수행합니다. OR 조인 조건인 a.c2 IN (b.c2, b.c3) 조건은 필터 조건으로 사용됩니다.
-- 2: 19.3
SELECT a.c1
, b.c2
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND a.c2 IN (b.c2, b.c3);
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN OUTER | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."C1"(+)="A"."C1")
filter("A"."C2"="B"."C2"(+) OR "A"."C2"="B"."C3"(+))
INSERT 문에 동일한 SELECT 문을 사용하면 t2 테이블이 Lateral View(VW_LAT)로 변환되고 t1, t2 테이블이 소트 머지 조인으로 아우터 조인을 수행합니다. 하지만 Predicate Information 항목에서 소트 머지 조인이 아닌 cartesian product으로 아우터 조인을 수행하는 것을 확인할 수 있습니다. 참고로 병렬 쿼리도 cartesian product으로 아우터 조인을 수행하며 실행 계획은 NL 조인이 표시됩니다.
-- 3: 19.3
INSERT
INTO t3
SELECT a.c1
, b.c2
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND a.c2 IN (b.c2, b.c3);
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | T3 |
| 2 | MERGE JOIN OUTER | |
| 3 | TABLE ACCESS FULL | T1 |
| 4 | BUFFER SORT | |
| 5 | VIEW | VW_LAT_DF9E236D |
|* 6 | TABLE ACCESS FULL | T2 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("B"."C1"="A"."C1" AND ("A"."C2"="B"."C2" OR "A"."C2"="B"."C3"))
아래는 21.3 버전의 실행 계획입니다. 앞선 19.3 버전의 SELECT 문처럼 해시 조인으로 아우터 조인이 수행됩니다.
-- 4: 21.3
INSERT
INTO t3
SELECT a.c1
, b.c2
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND a.c2 IN (b.c2, b.c3);
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | T3 |
|* 2 | HASH JOIN OUTER | |
| 3 | TABLE ACCESS FULL | T1 |
| 4 | TABLE ACCESS FULL | T2 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."C1"(+)="A"."C1")
filter("A"."C2"="B"."C2"(+) OR "A"."C2"="B"."C3"(+))
이 현상은 31009032 버그와 관련이 있습니다.
-- 5
SELECT bugno, value, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 31009032;
BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
-------- ----- ---------------------------------------------------- ------------------------
31009032 1 allow ANSI Rearchitecture for CTAS and INSERT-SELECT 21.1.0
1개의 행이 선택되었습니다.
관련 링크