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에서 추가되었음)