분산 쿼리와 서브 쿼리 팩토링

분산 쿼리에서 서브 쿼리 팩토링의 동작을 살펴보자.

테스트를 위해 아래와 같이 테이블과 DB 링크를 생성하자.

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

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

DROP DATABASE LINK dl1;
CREATE DATABASE LINK dl1 CONNECT TO tuna IDENTIFIED BY tuna USING 'ORA12CR2';

아래 쿼리는 전체 쿼리가 리모트에서 수행된다.

-- 2
WITH w1 AS (SELECT a.c1, b.c2 FROM t1@dl1 a, t2@dl1 b WHERE b.c1 = a.c1)
SELECT * FROM w1;

------------------------------------------------
| Id  | Operation              | Name | Inst   |
------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |        |
|*  1 |  HASH JOIN             |      |        |
|   2 |   TABLE ACCESS FULL    | T1   | ORA12~ |
|   3 |   TABLE ACCESS FULL    | T2   | ORA12~ |
------------------------------------------------

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

   1 - access("A1"."C1"="A2"."C1")

Note
-----
   - fully remote STATEMENT

아래 쿼리는 리모트에서 t1, t2 테이블을 읽어, 로컬에서 해시 조인을 수행하고, 결과를 임시 영역에 저장한다. 리모트에서 서브 쿼리를 MATERIALIZE 할 수 없기 때문이다.

-- 3
WITH w1 AS (SELECT a.c1, b.c2 FROM t1@dl1 a, t2@dl1 b WHERE b.c1 = a.c1)
SELECT * FROM w1 WHERE c1 = 1
UNION ALL
SELECT * FROM w1 WHERE c1 = 2;

-----------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |        |      |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |        |      |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6A9D_EB5ECA6 |        |      |
|*  3 |    HASH JOIN                             |                            |        |      |
|   4 |     REMOTE                               | T1                         |    DL1 | R->S | -- !
|   5 |     REMOTE                               | T2                         |    DL1 | R->S | -- !
|   6 |   UNION-ALL                              |                            |        |      |
|*  7 |    VIEW                                  |                            |        |      |
|   8 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6A9D_EB5ECA6 |        |      |
|*  9 |    VIEW                                  |                            |        |      |
|  10 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6A9D_EB5ECA6 |        |      |
-----------------------------------------------------------------------------------------------

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

   3 - access("B"."C1"="A"."C1")
   7 - filter("C1"=1)
   9 - filter("C1"=2)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "C1" FROM "T1" "A" (accessing 'DL1' )
   5 - SELECT "C1","C2" FROM "T2" "B" (accessing 'DL1' )

INLINE 힌트를 사용하면 전체 쿼리가 리모트에서 수행되는 것을 확인할 수 있다.

-- 4
WITH w1 AS (SELECT /*+ INLINE */ a.c1, b.c2 FROM t1@dl1 a, t2@dl1 b WHERE b.c1 = a.c1)
SELECT * FROM w1 WHERE c1 = 1
UNION ALL
SELECT * FROM w1 WHERE c1 = 2;

--------------------------------------------------------------
| Id  | Operation              | Name               | Inst   |
--------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                    |        |
|*  1 |  HASH JOIN             |                    |        |
|   2 |   VIEW                 | VW_JF_SET$37C1857C | ORA12~ |
|   3 |    UNION-ALL           |                    |        |
|*  4 |     TABLE ACCESS FULL  | T1                 | ORA12~ |
|*  5 |     TABLE ACCESS FULL  | T1                 | ORA12~ |
|   6 |   TABLE ACCESS FULL    | T2                 | ORA12~ |
--------------------------------------------------------------

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

   1 - access("A4"."C1"="ITEM_1")
   4 - filter("A5"."C1"=1)
   5 - filter("A3"."C1"=2)

Note
-----
   - fully remote STATEMENT