아우터 OR 조인 조건 #1

아우터 조인에 OR 조건을 조인 조건으로 사용하면 쿼리의 성능이 저하될 수 있다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, MOD (ROWNUM, 3) AS c2, 1 AS c3, 2 AS c4, 0 AS c5 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 2');

아래 쿼리는 t1 테이블의 c2 값에 따라 c3, c4, c5 열로 t2 테이블을 조인한다. 11.2 버전에서는 NL 조인으로 t2 테이블을 만번 반복 조회하여 30,667개의 블록 I/O가 발생한 것을 확인할 수 있다.

-- 2 : 11.2
SELECT a.c1 AS ac1, b.c1 AS bc1
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON (   (a.c2 = 1 AND b.c1 = a.c3)
        OR (a.c2 = 2 AND b.c1 = a.c4)
        OR (a.c2 = 0 AND b.c1 = a.c5));

----------------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |  10000 |   31363 |
|   1 |  NESTED LOOPS OUTER |      |      1 |  10000 |   31363 |
|   2 |   TABLE ACCESS FULL | T1   |      1 |  10000 |     696 |
|   3 |   VIEW              |      |  10000 |   6667 |   30667 |
|*  4 |    TABLE ACCESS FULL| T2   |  10000 |   6667 |   30667 |
----------------------------------------------------------------

아래는 12.2 버전의 실행계획이다. 소트 머지 조인으로 조인되었지만 여전히 t2 테이블을 만번 반복 조회했다. 블록 I/O도 2번 쿼리와 대동소이하다.

-- 3 : 12.2
SELECT a.c1 AS ac1, b.c1 AS bc1
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON (   (a.c2 = 1 AND b.c1 = a.c3)
        OR (a.c2 = 2 AND b.c1 = a.c4)
        OR (a.c2 = 0 AND b.c1 = a.c5));

----------------------------------------------------------------------------
| Id  | Operation            | Name            | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |      1 |  10000 |   30696 |
|   1 |  MERGE JOIN OUTER    |                 |      1 |  10000 |   30696 |
|   2 |   TABLE ACCESS FULL  | T1              |      1 |  10000 |     696 |
|   3 |   BUFFER SORT        |                 |  10000 |   6667 |   30000 |
|   4 |    VIEW              | VW_LAT_2E38C6CE |  10000 |   6667 |   30000 |
|*  5 |     TABLE ACCESS FULL| T2              |  10000 |   6667 |   30000 |
----------------------------------------------------------------------------

해법은 아래와 같이 열 별로 각각 t2 테이블을 조인하는 것이다. 블록 I/O가 705로 감소한 것을 확인할 수 있다.

-- 4
SELECT a.c1 AS ac1, COALESCE (b.c1, c.c1, d.c1) AS bc1
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON a.c2 = 1
   AND b.c1 = a.c3
  LEFT OUTER
  JOIN t2 c
    ON a.c2 = 2
   AND c.c1 = a.c4
  LEFT OUTER
  JOIN t2 d
    ON a.c2 = 0
   AND d.c1 = a.c5;

--------------------------------------------------------------------
| Id  | Operation               | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |  10000 |     705 |
|*  1 |  HASH JOIN RIGHT OUTER  |      |      1 |  10000 |     705 |
|   2 |   TABLE ACCESS FULL     | T2   |      1 |      2 |       3 |
|*  3 |   HASH JOIN RIGHT OUTER |      |      1 |  10000 |     702 |
|   4 |    TABLE ACCESS FULL    | T2   |      1 |      2 |       3 |
|*  5 |    HASH JOIN RIGHT OUTER|      |      1 |  10000 |     699 |
|   6 |     TABLE ACCESS FULL   | T2   |      1 |      2 |       3 |
|   7 |     TABLE ACCESS FULL   | T1   |      1 |  10000 |     696 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."C2"=CASE WHEN ("D"."C1" IS NOT NULL) THEN 0 ELSE 0 END AND "D"."C1"="A"."C5")
   3 - access("A"."C2"=CASE WHEN ("C"."C1" IS NOT NULL) THEN 2 ELSE 2 END AND "C"."C1"="A"."C4")
   5 - access("A"."C2"=CASE WHEN ("B"."C1" IS NOT NULL) THEN 1 ELSE 1 END AND "B"."C1"="A"."C3")

오라클 조인 문법으로는 위와 같은 아우터 조인을 수행할 수 없다.

-- 5
SELECT *
  FROM t1 a
    ,  t2 b
 WHERE (   (a.c2 = 1 AND b.c1(+) = a.c3)
        OR (a.c2 = 2 AND b.c1(+) = a.c4)
        OR (a.c2 = 0 AND b.c1(+) = a.c5));

ORA-01719: 포괄 조인 운영 (+)는 OR 또는 IN의 연산수를 허용하지 않습니다

아래와 같이 열 별로 조인해야 한다.

-- 6
SELECT a.c1 AS ac1, COALESCE (b.c1, c.c1, d.c1) AS bc1
  FROM t1 a, t2 b, t2 c, t2 d
 WHERE b.c1(+) = DECODE (a.c2, 1, a.c3)
   AND c.c1(+) = DECODE (a.c2, 2, a.c4)
   AND d.c1(+) = DECODE (a.c2, 0, a.c5);

--------------------------------------------------------------------
| Id  | Operation               | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |  10000 |     705 |
|*  1 |  HASH JOIN RIGHT OUTER  |      |      1 |  10000 |     705 |
|   2 |   TABLE ACCESS FULL     | T2   |      1 |      2 |       3 |
|*  3 |   HASH JOIN RIGHT OUTER |      |      1 |  10000 |     702 |
|   4 |    TABLE ACCESS FULL    | T2   |      1 |      2 |       3 |
|*  5 |    HASH JOIN RIGHT OUTER|      |      1 |  10000 |     699 |
|   6 |     TABLE ACCESS FULL   | T2   |      1 |      2 |       3 |
|   7 |     TABLE ACCESS FULL   | T1   |      1 |  10000 |     696 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."C1"=DECODE("A"."C2",0,"A"."C5"))
   3 - access("C"."C1"=DECODE("A"."C2",2,"A"."C4"))
   5 - access("B"."C1"=DECODE("A"."C2",1,"A"."C3"))