OUTER OR JOIN 조건 #2

예전에 OUTER OR JOIN 조건 글을 썼었다. 이번 글도 유사한 사례다.

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

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10000');

아래 쿼리가 개선 대상이다. b.c2 = 1인 경우 무조건 조인하는 형태다. 11.2 버전에서는 NL 조인, 12.2 버전에서는 소트 머지 조인으로 수행된다. 12.2 버전의 소트 머지 조인은 Lateral View를 조인하기 때문에 NL 조인과 동일하게 t2 테이블을 10000번 액세스한다.(Starts=10000)

-- 2-1
SELECT /*+ USE_HASH(A B) */
       COUNT (*)
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND (b.c2 = a.c2 OR b.c2 = 1);

-- 2-2 : 11.2
------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |      1 |00:00:03.69 |     200K|
|   1 |  SORT AGGREGATE      |      |      1 |      1 |00:00:03.69 |     200K|
|   2 |   NESTED LOOPS OUTER |      |      1 |  10000 |00:00:03.69 |     200K|
|   3 |    TABLE ACCESS FULL | T1   |      1 |  10000 |00:00:00.01 |      21 |
|   4 |    VIEW              |      |  10000 |  10000 |00:00:03.68 |     200K|
|*  5 |     TABLE ACCESS FULL| T2   |  10000 |  10000 |00:00:03.68 |     200K|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("B"."C1"="A"."C1" AND ("B"."C2"="A"."C2" OR "B"."C2"=1)))

-- 2-3 : 12.2
------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |      1 |00:00:03.67 |     290K|
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |00:00:03.67 |     290K|
|   2 |   MERGE JOIN OUTER    |                 |      1 |  10000 |00:00:03.67 |     290K|
|   3 |    TABLE ACCESS FULL  | T1              |      1 |  10000 |00:00:00.01 |      30 |
|   4 |    BUFFER SORT        |                 |  10000 |  10000 |00:00:03.65 |     290K|
|   5 |     VIEW              | VW_LAT_2E38C6CE |  10000 |  10000 |00:00:03.62 |     290K|
|*  6 |      TABLE ACCESS FULL| T2              |  10000 |  10000 |00:00:03.61 |     290K|
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("B"."C1"="A"."C1" AND ("B"."C2"=1 OR "B"."C2"="A"."C2")))

Outline Data
-------------
      USE_MERGE_CARTESIAN(@"SEL$49A20730" "VW_LAT_F486F43F"@"SEL$F486F43F")

해법은 IS NULL OR 방식 옵션 조건 처리 글과 동일하다. OR 조건이 포함된 조인 조건을 CASE 표현식으로 변경하면 된다.

-- 3
SELECT /*+ USE_HASH(A B) */
       COUNT (*)
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND CASE WHEN (b.c2 = a.c2 OR b.c2 = 1) THEN 1 END = 1;

--------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |      1 |00:00:00.01 |      59 |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |00:00:00.01 |      59 |
|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |  10000 |00:00:00.01 |      59 |
|   3 |    TABLE ACCESS FULL   | T2   |      1 |  10000 |00:00:00.01 |      29 |
|   4 |    TABLE ACCESS FULL   | T1   |      1 |  10000 |00:00:00.01 |      30 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."C1"="A"."C1")
       filter(CASE "B"."C2" WHEN "A"."C2" THEN 1 WHEN 1 THEN 1 END =1)