Join pedicate Pushing

Join Predicate Pushing은 Join 조건이 View 내부로 Pushing 되는 현상을 의미

no_push_pred 힌트를 사용하여 Join Predicate Pushing을 막은 경우
t3.c1 = v.c1(+) 조건으로 인해 View 안으로 Pushing 되지 못하므로 인라인 뷰와 Main Query가 별개로 최적화 됨


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"

t3.c1 = v.c1(+) 조건이 인라인 뷰안으로 Pushing 가능하다면


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

t3.c3 = 1 조건에 의해 filtering 된 로우에 대해서만 Inline View내부에서 T2와 T3가 조인됨
8번과 9번 단계의 Predicate으로 Pushing된 것을 확인 할 수 있음
4번 단계에서 VIEW PUSHED PREDICATE 으로도 확인 가능함
HASH JOIN (OUTER)가 NESTED LOOPS (OUTER)로 바뀌면서 만족하는 데이터만 view안으로 Pushing

Union all을 view안에 사용하면 View Merging은 실패하지만 Join Predicate Pushing으로 인해 효율적으로 Index Range Scan과
NL Join을 사용할 수 있음
이 과정에서 Union All Partition 이라는 새로운 이름의 새로운 Operation이 등장함(Oracle 10g에서 추가되었음)

Join Predicate Pushing (by code0112) [2011.04.29]

Join pedicate Pushing