아우터 조인과 OR 조건

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

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

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

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER);

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);

INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (3, 1);
INSERT INTO t1 VALUES (4, 2);
INSERT INTO t2 VALUES (1, 2);
INSERT INTO t2 VALUES (2, 1);
COMMIT;

아래는 이너 조인을 사용한 쿼리다. 내부적으로 USE_CONCAT 힌트가 사용되어 최적의 실행 계획이 생성되었다.

-- 2
SELECT *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND (a.c2 = 1 OR b.c2 = 1);

C1 C2 C1 C2
-- -- -- --
 1  1  1  2
 2  2  2  1

2 행이 선택되었습니다.

------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|   1 |  CONCATENATION                 |       |
|   2 |   NESTED LOOPS                 |       |
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  5 |      INDEX RANGE SCAN          | T2_X2 |
|*  6 |     INDEX RANGE SCAN           | T1_X1 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T1    |
|   8 |   NESTED LOOPS                 |       |
|   9 |    NESTED LOOPS                |       |
|  10 |     TABLE ACCESS BY INDEX ROWID| T1    |
|* 11 |      INDEX RANGE SCAN          | T1_X2 |
|* 12 |     INDEX RANGE SCAN           | T2_X1 |
|* 13 |    TABLE ACCESS BY INDEX ROWID | T2    |
------------------------------------------------

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

   5 - access("B"."C2"=1)
   6 - access("B"."C1"="A"."C1")
  11 - access("A"."C2"=1)
  12 - access("B"."C1"="A"."C1")
  13 - filter(LNNVL("B"."C2"=1))

아래는 아우터 조인을 사용한 쿼리다. 조인 조건을 정적 조건으로 사용하고, 일반 조건을 동적 조건으로 사용하는 방식이다. 아우터 조인을 수행한 결과를 필터링하는 방식으로 처리되어 쿼리의 성능이 저하될 수 있다.

-- 3
SELECT *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1         -- 정적 조건
   AND (a.c2 = 1 OR b.c2 = 1) -- 동적 조건
;

C1 C2 C1 C2
-- -- -- --
 1  1  1  2
 2  2  2  1
 3  1

3 행이 선택되었습니다.

-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|*  1 |  FILTER                       |       |
|   2 |   NESTED LOOPS OUTER          |       |
|   3 |    TABLE ACCESS FULL          | T1    | -- !
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |
|*  5 |     INDEX RANGE SCAN          | T2_X1 |
-----------------------------------------------

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

   1 - filter("A"."C2"=1 OR "B"."C2"=1)
   5 - access("B"."C1"(+)="A"."C1")

아래는 USE_CONCAT 힌트를 사용한 쿼리다. 실행 계획 2, 4번에서 b.c2 = 1 조건이 사용되지 못하는 것을 확인할 수 있다. t1 테이블이 아우터 기준이기 때문에 NL 조인 시 t2 테이블이 드라이빙될 수 없기 때문이다.

-- 4
SELECT /*+ USE_CONCAT */
       *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1
   AND (a.c2 = 1 OR b.c2 = 1);

------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|   1 |  CONCATENATION                 |       |
|*  2 |   FILTER                       |       |
|   3 |    NESTED LOOPS OUTER          |       |
|   4 |     TABLE ACCESS FULL          | T1    | -- !
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  6 |      INDEX RANGE SCAN          | T2_X1 |
|*  7 |   FILTER                       |       |
|   8 |    NESTED LOOPS OUTER          |       |
|   9 |     TABLE ACCESS BY INDEX ROWID| T1    |
|* 10 |      INDEX RANGE SCAN          | T1_X2 |
|  11 |     TABLE ACCESS BY INDEX ROWID| T2    |
|* 12 |      INDEX RANGE SCAN          | T2_X1 |
------------------------------------------------

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

   2 - filter("B"."C2"=1)
   6 - access("B"."C1"(+)="A"."C1")
   7 - filter(LNNVL("B"."C2"=1))
  10 - access("A"."C2"=1)
  12 - access("B"."C1"(+)="A"."C1")

OUTER_JOIN_TO_INNER 힌트를 사용해도 원하는 결과를 얻을 수 없다. 쿼리 변환의 내부적인 절차에 의한 제한으로 추측된다.

-- 5
SELECT /*+ USE_CONCAT OUTER_JOIN_TO_INNER(@SEL$1_1) */
       *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1
   AND (a.c2 = 1 OR b.c2 = 1);

------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|   1 |  CONCATENATION                 |       |
|*  2 |   FILTER                       |       |
|   3 |    NESTED LOOPS OUTER          |       |
|   4 |     TABLE ACCESS FULL          | T1    | -- !
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  6 |      INDEX RANGE SCAN          | T2_X1 |
|*  7 |   FILTER                       |       |
|   8 |    NESTED LOOPS OUTER          |       |
|   9 |     TABLE ACCESS BY INDEX ROWID| T1    |
|* 10 |      INDEX RANGE SCAN          | T1_X2 |
|  11 |     TABLE ACCESS BY INDEX ROWID| T2    |
|* 12 |      INDEX RANGE SCAN          | T2_X1 |
------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1_1 / A@SEL$1
   5 - SEL$1_1 / B@SEL$1
   6 - SEL$1_1 / B@SEL$1
   9 - SEL$1_2 / A@SEL$1_2
  10 - SEL$1_2 / A@SEL$1_2
  11 - SEL$1_2 / B@SEL$1_2
  12 - SEL$1_2 / B@SEL$1_2

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

   2 - filter("B"."C2"=1)
   6 - access("B"."C1"(+)="A"."C1")
   7 - filter(LNNVL("B"."C2"=1))
  10 - access("A"."C2"=1)
  12 - access("B"."C1"(+)="A"."C1")

12.1 버전까지는 아래처럼 쿼리를 분리하여 쿼리의 성능을 개선해야 한다.

-- 6
SELECT *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c1
   AND b.c2 = 1
   AND LNNVL (a.c2 = 1)
UNION ALL
SELECT *
  FROM t1 a, t2 b
 WHERE a.c2 = 1
   AND b.c1(+) = a.c1;

C1 C2 C1 C2
-- -- -- --
 2  2  2  1
 1  1  1  2
 3  1

3 행이 선택되었습니다.

------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|   1 |  UNION-ALL                     |       |
|   2 |   NESTED LOOPS                 |       |
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  5 |      INDEX RANGE SCAN          | T2_X2 | -- !
|*  6 |     INDEX RANGE SCAN           | T1_X1 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | T1    |
|   8 |   NESTED LOOPS OUTER           |       |
|   9 |    TABLE ACCESS BY INDEX ROWID | T1    |
|* 10 |     INDEX RANGE SCAN           | T1_X2 |
|  11 |    TABLE ACCESS BY INDEX ROWID | T2    |
|* 12 |     INDEX RANGE SCAN           | T2_X1 |
------------------------------------------------

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

   5 - access("B"."C2"=1)
   6 - access("B"."C1"="A"."C1")
   7 - filter(LNNVL("A"."C2"=1))
  10 - access("A"."C2"=1)
  12 - access("B"."C1"(+)="A"."C1")

12.2 버전부터는 OR_EXPAND 힌트를 사용하여 최적화된 실행 계획을 생성할 수 있다.

-- 7
SELECT /*+ OR_EXPAND */
       *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1
   AND (a.c2 = 1 OR b.c2 = 1);

-------------------------------------------------------------------
| Id  | Operation                               | Name            |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |
|   1 |  VIEW                                   | VW_ORE_EAA77DC2 |
|   2 |   UNION-ALL                             |                 |
|   3 |    NESTED LOOPS OUTER                   |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | T1              |
|*  5 |      INDEX RANGE SCAN                   | T1_X2           |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | T2              |
|*  7 |      INDEX RANGE SCAN                   | T2_X1           |
|   8 |    NESTED LOOPS                         |                 |
|   9 |     NESTED LOOPS                        |                 |
|  10 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2              |
|* 11 |       INDEX RANGE SCAN                  | T2_X2           | -- !
|* 12 |      INDEX RANGE SCAN                   | T1_X1           |
|* 13 |     TABLE ACCESS BY INDEX ROWID         | T1              |
-------------------------------------------------------------------

Outline Data
-------------

      OUTER_JOIN_TO_INNER(@"SET$9162BF3C_2" "B"@"SEL$1")

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

   5 - access("A"."C2"=1)
   7 - access("B"."C1"(+)="A"."C1")
  11 - access("B"."C2"=1)
  12 - access("B"."C1"="A"."C1")
  13 - filter(LNNVL("A"."C2"=1))