MERGE JOIN CARTESIAN

조인 조건을 누락하지 않았는데도 MERGE JOIN CARTESIAN이 발생하는 현상에 대한 문의를 받았다.

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

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1, 'X' AS c2 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT CEIL (c1 / 100) AS c1, c2 FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t3_x1 ON t3 (c1);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'T2');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'T3');

아래는 조인 조건이 누락된 쿼리다. MERGE JOIN CARTESIAN이 발생한다. MERGE JOIN CARTESIAN은 MERGE JOIN과 원리가 같다. 2-2번 쿼리처럼 이너 테이블(t2)를 PGA 영역에 저장하고 반복 액세스함으로써 블록 I/O를 감소시키는 방식이다. 4번 오퍼레이션인 BUFFER SORT를 1000번 액세스한 것을 확인할 수 있다.(Starts = 1000) 2-3번 쿼리는 NL 조인으로 수행되어 t2 테이블을 1000번 반복 액세스했기 때문에 과도한 블록 I/O가 발생했다.

-- 2-1
SELECT * FROM t1, t2;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  MERGE JOIN CARTESIAN|      |
|   2 |   TABLE ACCESS FULL  | T1   |
|   3 |   BUFFER SORT        |      |
|   4 |    TABLE ACCESS FULL | T2   |
-------------------------------------

-- 2-2
SELECT COUNT (*) FROM t1, t2;

---------------------------------------------------------
| Id  | Operation             | Name | Starts | Buffers |
---------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |       8 |
|   1 |  SORT AGGREGATE       |      |      1 |       8 |
|   2 |   MERGE JOIN CARTESIAN|      |      1 |       8 |
|   3 |    TABLE ACCESS FULL  | T1   |      1 |       4 |
|   4 |    BUFFER SORT        |      |   1000 |       4 | -- !
|   5 |     TABLE ACCESS FULL | T2   |      1 |       4 |
---------------------------------------------------------

-- 2-3
SELECT /*+ USE_NL(T2) */ COUNT (*) FROM t1, t2;

-------------------------------------------------------
| Id  | Operation           | Name | Starts | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |    4004 |
|   1 |  SORT AGGREGATE     |      |      1 |    4004 |
|   2 |   NESTED LOOPS      |      |      1 |    4004 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |       4 |
|   4 |    TABLE ACCESS FULL| T2   |   1000 |    4000 | -- !
-------------------------------------------------------

아래 쿼리는 조인 조건을 기술했는데도 MERGE JOIN CARTESIAN이 발생했다. 아우터 테이블(t1)의 카디널리티가 1로 예상되었고, 조건 이행에 의해 이너 테이블(t2)이 별도로 액세스할 수 있었기 때문이다. 아우터 테이블이 1건이므로 이너 테이블도 1번만 액세스하면 되기 때문에 MERGE JOIN CARTESIAN 방식으로 수행되는 것이다. 3-2번 쿼리는 CARDINALITY 힌트로 아우터 테이블의 카디널리티를 2로 설정했다. 해시 조인으로 조인되는 것을 확인할 수 있다. 3-1번 쿼리는 t2 테이블을 반복 액세스하진 않지만 이너 테이블을 PGA에 저장하는 과정이 필요하다. 3-3번 쿼리처럼 NL 조인으로 수행하는 편이 성능 측면에서 유리할 수 있다.

-- 3-1
SELECT *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c1 = a.c1;

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |   100 |
|   1 |  MERGE JOIN CARTESIAN         |       |   100 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 | -- !
|*  3 |    INDEX RANGE SCAN           | T1_X1 |     1 |
|   4 |   BUFFER SORT                 |       |   100 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2    |   100 |
|*  6 |     INDEX RANGE SCAN          | T2_X1 |   100 |
-------------------------------------------------------

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

   3 - access("A"."C1"=1)
   6 - access("B"."C1"=1)

-- 3-2
SELECT /*+ CARDINALITY(A 2) */
       *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c1 = a.c1;

------------------------------------------------------
| Id  | Operation                    | Name  | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   200 |
|*  1 |  HASH JOIN                   |       |   200 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     2 | -- !
|*  3 |    INDEX RANGE SCAN          | T1_X1 |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2    |   100 |
|*  5 |    INDEX RANGE SCAN          | T2_X1 |   100 |
------------------------------------------------------

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

   1 - access("B"."C1"="A"."C1")
   3 - access("A"."C1"=1)
   5 - access("B"."C1"=1)

-- 3-3
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c1 = a.c1;

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |   100 |
|   1 |  NESTED LOOPS                 |       |       |
|   2 |   NESTED LOOPS                |       |   100 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |
|*  4 |     INDEX RANGE SCAN          | T1_X1 |     1 |
|*  5 |    INDEX RANGE SCAN           | T2_X1 |   100 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2    |   100 |
-------------------------------------------------------

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

   4 - access("A"."C1"=1)
   5 - access("B"."C1"=1)

아래 4-1번 쿼리도 조인 조건을 기술했지만 MERGE JOIN CARTESIAN이 발생했다. ORDERED 힌트로 인해 t1, t2 ,t3 순서로 조인을 수행해야 하지만 t1, t2의 조인 조건이 없었기 때문이다. 일반 조건과 달리 조인 조건은 이행되지 않는다. 4-2번 쿼리는 조인 조건을 변경했고, 4-3번 쿼리는 힌트를 제거했다. 두 쿼리 모두 MERGE JOIN CARTESIAN이 발생하지 않는 것을 확인할 수 있다.

-- 4-1
SELECT /*+ ORDERED */
       *
  FROM t1 a, t2 b, t3 c
 WHERE c.c1 = a.c1
   AND c.c1 = b.c1;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|   2 |   TABLE ACCESS FULL   | T3   |
|   3 |   MERGE JOIN CARTESIAN|      |
|   4 |    TABLE ACCESS FULL  | T1   |
|   5 |    BUFFER SORT        |      |
|   6 |     TABLE ACCESS FULL | T2   |
--------------------------------------

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

   1 - access("C"."C1"="A"."C1" AND "C"."C1"="B"."C1")

-- 4-2
SELECT /*+ ORDERED */
       *
  FROM t1 a, t2 b, t3 c
 WHERE b.c1 = a.c1 -- !
   AND c.c1 = b.c1;

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|*  1 |  HASH JOIN          |      |
|*  2 |   HASH JOIN         |      |
|   3 |    TABLE ACCESS FULL| T1   |
|   4 |    TABLE ACCESS FULL| T2   |
|   5 |   TABLE ACCESS FULL | T3   |
------------------------------------

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

   1 - access("C"."C1"="B"."C1")
   2 - access("B"."C1"="A"."C1")

-- 4-3
SELECT *
  FROM t1 a, t2 b, t3 c
 WHERE c.c1 = a.c1
   AND c.c1 = b.c1;

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|*  1 |  HASH JOIN          |      |
|*  2 |   HASH JOIN         |      |
|   3 |    TABLE ACCESS FULL| T1   |
|   4 |    TABLE ACCESS FULL| T3   |
|   5 |   TABLE ACCESS FULL | T2   |
------------------------------------

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

   1 - access("C"."C1"="B"."C1")
   2 - access("C"."C1"="A"."C1")