JPPD에 의해 조인 순서가 이상 동작하는 사례를 살펴보자.
테스트를 위해 아래와 같이 테이블과 뷰를 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE OR REPLACE VIEW v2 AS
SELECT * FROM t2 UNION ALL
SELECT * FROM t3;
CREATE INDEX t1_x1 ON t1 (c2);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t3_x1 ON t3 (c1);
CREATE INDEX t4_x1 ON t4 (c1);
아래 쿼리는 JPPD가 정상적으로 동작한다.
-- 2
SELECT *
FROM t1 a
, v2 b
WHERE a.c2 = 1
AND b.c1 = a.c1;
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 3 | INDEX RANGE SCAN | T1_X1 |
| 4 | VIEW | V2 |
| 5 | UNION ALL PUSHED PREDICATE | | -- !
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 7 | INDEX RANGE SCAN | T2_X1 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 9 | INDEX RANGE SCAN | T3_X1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."C2"=1)
7 - access("C1"="A"."C1")
9 - access("C1"="A"."C1")
아래 쿼리는 JPPD가 수행된 뷰(v2)의 열을 t3 테이블에 조인 조건(c.c1 = b.c1)으로 사용했다. JPPD가 동작하지 않는 것을 확인할 수 있다.
-- 3
SELECT *
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1 = b.c1 -- !
;
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 |
|* 5 | INDEX RANGE SCAN | T1_X1 |
| 6 | VIEW | V2 |
| 7 | UNION-ALL | | -- !!
| 8 | TABLE ACCESS FULL | T2 |
| 9 | TABLE ACCESS FULL | T3 |
|* 10 | INDEX RANGE SCAN | T3_X1 |
| 11 | TABLE ACCESS BY INDEX ROWID | T3 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."C1"="A"."C1")
5 - access("A"."C2"=1)
10 - access("C"."C1"="B"."C1")
PUSH_PRED 힌트를 사용하면 t3 테이블이 먼저 조인되어 카티션 곱이 발생하는 것을 확인할 수 있다. ORDERED 힌트 마저 무시되었다.
-- 4
SELECT /*+ ORDERED USE_NL(B) PUSH_PRED(B) */
*
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1 = b.c1;
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | MERGE JOIN CARTESIAN | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 4 | INDEX RANGE SCAN | T1_X1 |
| 5 | BUFFER SORT | |
| 6 | TABLE ACCESS FULL | T3 | -- !!
| 7 | VIEW | V2 |
| 8 | UNION ALL PUSHED PREDICATE | | -- !
|* 9 | FILTER | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 11 | INDEX RANGE SCAN | T2_X1 |
|* 12 | FILTER | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 14 | INDEX RANGE SCAN | T3_X1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."C2"=1)
9 - filter("C"."C1"="A"."C1")
11 - access("C1"="A"."C1")
12 - filter("C"."C1"="A"."C1")
14 - access("C1"="A"."C1")
Outline Data
-------------
/*+
PUSH_PRED(@"SEL$1" "B"@"SEL$1" 3 2)
*/
_optimizer_push_pred_cost_based 파라미터를 false로 설정하면 이전 방식의 JPPD가 동작하는 것을 확인할 수 있다.1 4번 쿼리의 실행 계획은 비용기반 JPPD의 오동작으로 인해 것임을 유추할 수 있다.
-- 5
SELECT /*+ ORDERED USE_NL(B) OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED', 'FALSE') */
*
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1 = b.c1;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 5 | INDEX RANGE SCAN | T1_X1 |
| 6 | VIEW | V2 |
| 7 | UNION-ALL PARTITION | | -- !
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 9 | INDEX RANGE SCAN | T2_X1 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 11 | INDEX RANGE SCAN | T3_X1 |
|* 12 | INDEX RANGE SCAN | T3_X1 |
| 13 | TABLE ACCESS BY INDEX ROWID | T3 |
----------------------------------------------------------
Outline Data
-------------
/*+
OLD_PUSH_PRED(@"SEL$1" "B"@"SEL$1" ("T3"."C1"))
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."C2"=1)
9 - access("C1"="A"."C1")
11 - access("C1"="A"."C1")
12 - access("C"."C1"="B"."C1")
쿼리가 단순하다면 인라인 뷰를 통해 JPPD를 제어할 수 있다.
-- 6
SELECT *
FROM (SELECT /*+ NO_MERGE */
a.c1 AS ac1, a.c2 AS ac2, b.c1 AS bc1, b.c2 AS bc2
FROM t1 a, v2 b
WHERE a.c2 = 1
AND b.c1 = a.c1) a
, t3 c
WHERE c.c1 = a.bc1;
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | VIEW | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 6 | INDEX RANGE SCAN | T1_X1 |
| 7 | VIEW | V2 |
| 8 | UNION ALL PUSHED PREDICATE | | -- !
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 10 | INDEX RANGE SCAN | T2_X1 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 12 | INDEX RANGE SCAN | T3_X1 |
|* 13 | INDEX RANGE SCAN | T3_X1 |
| 14 | TABLE ACCESS BY INDEX ROWID | T3 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."C2"=1)
10 - access("C1"="A"."C1")
12 - access("C1"="A"."C1")
13 - access("C"."C1"="A"."BC1")
아래 쿼리는 t3 테이블을 아우터 조인하고 NO_OUTER_JOIN_TO_INNER 힌트를 통해 아우터 조인 후 필터링하는 방식을 사용했다. JPPD가 정상적으로 동작하는 것을 확인할 수 있다.
-- 7
SELECT /*+ NO_OUTER_JOIN_TO_INNER */
*
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1(+) = b.c1 -- !
AND c.c1 IS NOT NULL -- !
;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | NESTED LOOPS OUTER | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 5 | INDEX RANGE SCAN | T1_X1 |
| 6 | VIEW | V2 |
| 7 | UNION ALL PUSHED PREDICATE | | -- !
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 9 | INDEX RANGE SCAN | T2_X1 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 11 | INDEX RANGE SCAN | T3_X1 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | T3 |
|* 13 | INDEX RANGE SCAN | T3_X1 |
----------------------------------------------------------
Outline Data
-------------
/*+
PUSH_PRED(@"SEL$1" "B"@"SEL$1" 2)
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"."C1" IS NOT NULL)
5 - access("A"."C2"=1)
9 - access("C1"="A"."C1")
11 - access("C1"="A"."C1")
13 - access("C"."C1"(+)="B"."C1")
4번, 7번 쿼리의 Outline Data를 참조하면 아래의 힌트를 통해 JPPD를 유도할 수도 있다. 쿼리가 복잡한 경우 제대로 동작하지 않을 수 있으므로 5번 쿼리 방식을 사용하는 편이 쉽다.
-- 8
SELECT /*+ PUSH_PRED(B 2) NO_PUSH_PRED(B 3) */
*
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1 = b.c1;
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 |
|* 5 | INDEX RANGE SCAN | T1_X1 |
| 6 | VIEW | V2 |
| 7 | UNION ALL PUSHED PREDICATE | | -- !
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 9 | INDEX RANGE SCAN | T2_X1 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 11 | INDEX RANGE SCAN | T3_X1 |
|* 12 | INDEX RANGE SCAN | T3_X1 |
| 13 | TABLE ACCESS BY INDEX ROWID | T3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."C2"=1)
9 - access("C1"="A"."C1")
11 - access("C1"="A"."C1")
12 - access("C"."C1"="B"."C1")
조인 순서를 변경할 수 있다면 손쉽게 문제를 해결할 수 있다.
-- 9
SELECT *
FROM t1 a
, v2 b
, t3 c
WHERE a.c2 = 1
AND b.c1 = a.c1
AND c.c1 = a.c1 -- !
;
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 |
|* 4 | INDEX RANGE SCAN | T1_X1 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T3 |
|* 6 | INDEX RANGE SCAN | T3_X1 |
| 7 | VIEW | V2 |
| 8 | UNION ALL PUSHED PREDICATE | | -- !
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 10 | INDEX RANGE SCAN | T2_X1 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
|* 12 | INDEX RANGE SCAN | T3_X1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."C2"=1)
6 - access("C"."C1"="A"."C1")
10 - access("C1"="A"."C1")
12 - access("C1"="A"."C1")
- _push_join_union_view 파라미터를 false로 설정해도 이전 방식의 JPPD가 동작한다. 단, 파라미터가 true로 설정된 상태로 OLD_PUSH_PRED 힌트를 사용하면 JPPD가 동작하지 않는다. [본문으로]
'Oracle > Tuning' 카테고리의 다른 글
| NOT EXISTS 서브 쿼리가 UNNEST되지 않는 사례 (0) | 2020.02.21 |
|---|---|
| BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 (0) | 2020.02.18 |
| 분산 쿼리와 서브 쿼리 팩토링 (0) | 2020.02.11 |
| JPPD와 분석 함수 (0) | 2020.02.11 |
| JPPD와 DB 링크 (0) | 2020.01.20 |
