select /*+ gather_plan_statistics no_push_pred(v) */
t3.c1, v.c2
from
t3,
(select t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
from t1, t2
where t1.c1 = t2.c1
order by t1.c1, t2.c2) v
where
t3.c1 = v.c1(+) and
t3.c3 = 1
;
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=1 Bytes=51)
1 0 HASH JOIN (OUTER) (Cost=18 Card=1 Bytes=51)
2 1 TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=2 Card=1 Bytes=26)
3 1 VIEW (Cost=15 Card=1000 Bytes=24K)
4 3 SORT (ORDER BY) (Cost=15 Card=1000 Bytes=50K)
5 4 HASH JOIN (Cost=14 Card=1000 Bytes=50K)
6 5 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=1000 Bytes=24K)
7 5 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=10 Card=10K Bytes=254K)
1 - ACCESS PREDICATES "T3"."C1"="V"."C1"(+)
3 - FILTER PREDICATES "T3"."C3"=1
5 - ACCESS PREDICATES "T1"."C1"="T2"."C1"
select /*+ gather_plan_statistics */
t3.c1, v.c2
from
t3,
(select t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
from t1, t2
where t1.c1 = t2.c1
order by t1.c1, t2.c2) v
where
t3.c1 = v.c1(+) and
t3.c3 = 1
;
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=10 Bytes=550)
1 0 NESTED LOOPS (OUTER) (Cost=14 Card=10 Bytes=550)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T3' (TABLE) (Cost=9 Card=1 Bytes=39)
3 2 INDEX (RANGE SCAN) OF 'T3_N2' (INDEX) (Cost=1 Card=1)
4 1 VIEW PUSHED PREDICATE (Cost=5 Card=1 Bytes=16)
5 4 SORT (ORDER BY) (Cost=5 Card=1 Bytes=64)
6 5 NESTED LOOPS (Cost=4 Card=1 Bytes=64)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2 Card=1 Bytes=25)
8 7 INDEX (RANGE SCAN) OF 'T2_N1' (INDEX) (Cost=1 Card=1)
9 6 INDEX (RANGE SCAN) OF 'T1_N1' (INDEX) (Cost=1 Card=1)
8 - ACCESS PREDICATES "T2"."C1"="T3"."C1"
9 - ACCESS PREDICATES "T1"."C1"="T3"."C1" FILTER PREDICATES "T1"."C1"="T2"."C1"
1 - ACCESS PREDICATES "T3"."C3"=1
- 강좌 URL : http://www.gurubee.net/lecture/3929
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.