Oracle/Tuning2020. 2. 11. 23:51

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

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


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

1
2
3
4
5
6
7
8
9
-- 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';

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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 할 수 없기 때문이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 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 힌트를 사용하면 전체 쿼리가 리모트에서 수행되는 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 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


저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션  (0) 2020.02.18
JPPD에 의한 조인 순서의 이상 동작  (0) 2020.02.12
JPPD와 분석 함수  (0) 2020.02.11
JPPD와 DB 링크  (0) 2020.01.20
소트 머지 조인의 조인 조건에 따른 수행 방식 차이  (0) 2019.12.28
Posted by 정희락_

티스토리툴바