개요
OR 조인 조건이 있는 아우터 조인은 비효율적인 실행 계획으로 인해 성능이 저하될 수 있습니다. OR 조인 조건이 아닌 다른 조인 조건이 있는 경우 OR 조인 조건을 CASE 표현식으로 변경하여 성능을 개선할 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER);
직렬 쿼리
아래 쿼리는 OR 조인 조건을 처리하기 위해 t2 테이블을 Lateral View로 변환하고 소트 머지 조인으로 아우터 조인을 수행합니다. 하지만 Predicate Information 항목에서 소트 머지 조인이 아닌 cartesian product으로 아우터 조인을 수행하는 것을 확인할 수 있습니다.
-- 2
SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND ((a.c2 = 1 AND b.c2 = a.c3) OR (a.c2 = 2 AND b.c2 = a.c4));
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN OUTER | |
| 2 | TABLE ACCESS FULL | T1 |
| 3 | BUFFER SORT | |
| 4 | VIEW | VW_LAT_2E38C6CE |
|* 5 | TABLE ACCESS FULL| T2 |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("B"."C1"="A"."C1" AND ("B"."C2"="A"."C3" AND "A"."C2"=1 OR
"B"."C2"="A"."C4" AND "A"."C2"=2))
아래와 같이 쿼리를 재작성할 수 있지만 복잡한 쿼리인 경우 재작성이 어려울 수 있고, 테이블 중복 액세스로 인해 오히려 성능이 저하될 수도 있습니다.
-- 3
SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND b.c2 = a.c3
WHERE a.c2 = 1
UNION ALL
SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND b.c2 = a.c4
WHERE a.c2 = 2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | UNION-ALL | |
|* 2 | HASH JOIN OUTER | |
|* 3 | TABLE ACCESS FULL| T1 |
| 4 | TABLE ACCESS FULL| T2 |
|* 5 | HASH JOIN OUTER | |
|* 6 | TABLE ACCESS FULL| T1 |
| 7 | TABLE ACCESS FULL| T2 |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."C1"(+)="A"."C1" AND "B"."C2"(+)="A"."C3")
3 - filter("A"."C2"=1)
5 - access("B"."C1"(+)="A"."C1" AND "B"."C2"(+)="A"."C4")
6 - filter("A"."C2"=2)
아래 쿼리는 OR 조인 조건을 CASE 표현식으로 변경하여 해시 조인으로 아우터 조인을 수행합니다. 참고로 OR 조인 조건이 아닌 다른 조인 조건만으로 효율적인 해시 조인이 가능해야 성능이 저하되지 않습니다.
-- 4
SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND CASE WHEN ((a.c2 = 1 AND b.c2 = a.c3) OR (a.c2 = 2 AND b.c2 = a.c4)) THEN 1 END = 1;
-----------------------------------
| 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(CASE WHEN (("A"."C2"=1 AND "B"."C2"(+)="A"."C3") OR
("A"."C2"=2 AND "B"."C2"(+)="A"."C4")) THEN 1 END =1)
병렬 쿼리
병렬 쿼리는 소트 머지 조인이 아닌 NL 조인으로 아우터 조인을 수행합니다. Predicate Information 항목을 살펴보면 직렬 쿼리와 마찬가지로 cartesian product으로 조인을 수행하는 것을 확인할 수 있습니다.
-- 5
SELECT /*+ PARALLEL(2) */
a.c1
, b.c2
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND ((a.c2 = 1 AND b.c2 = a.c3) OR (a.c2 = 2 AND b.c2 = a.c4));
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS OUTER | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 6 | VIEW | VW_LAT_2E38C6CE | Q1,00 | PCWP | |
|* 7 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("B"."C1"="A"."C1" AND ("B"."C2"="A"."C3" AND "A"."C2"=1 OR "B"."C2"="A"."C4" AND "A"."C2"=2))
병렬 쿼리도 OR 조인 조건을 CASE 표현식으로 변경하면 해시 조인으로 아우터 조인을 수행할 수 있습니다.
-- 6
SELECT /*+ PARALLEL(2) */
a.c1
, b.c2
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c1
AND CASE WHEN ((a.c2 = 1 AND b.c2 = a.c3) OR (a.c2 = 2 AND b.c2 = a.c4)) THEN 1 END = 1;
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
|* 3 | HASH JOIN OUTER | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 6 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."C1"(+)="A"."C1")
filter(CASE WHEN (("A"."C2"=1 AND "B"."C2"(+)="A"."C3") OR ("A"."C2"=2 AND
"B"."C2"(+)="A"."C4")) THEN 1 END =1)