We'll, we don't push the predicate down because that would change the meaning of the query in this case. You seem to thing that your two queries should be equivalent -- but they are not. Consider (assume your tables were empty):
ops$tkyte@ORA815> insert into fk_tab_master values ( 1 );
1 row created.
ops$tkyte@ORA815>
ops$tkyte@ORA815> insert into drive_tab values ( 100, null );
1 row created.
ops$tkyte@ORA815>
ops$tkyte@ORA815> select count(*)
2 from fk_tab_master mst,
3 fk_tab_detail det,
4 drive_tab drv
5 Where mst.fk_refno = det.fk_refno
6 and drv.fk_det_refno = det.fk_det_refno (+)
7 /
COUNT(*)
----------
0
ops$tkyte@ORA815>
ops$tkyte@ORA815> select /*+ push_join_pred( il1 ) */ count(*)
2 from ( select mst.fk_refno,
3 det.fk_det_refno
4 from fk_tab_master mst,
5 fk_tab_detail det
6 where mst.fk_refno = det.fk_refno
7 ) il1,
8 drive_tab drv
9 where drv.fk_det_refno = il1.fk_det_refno (+)
10 /
COUNT(*)
----------
1
They give different answers. Looking at the query:
select count(*)
from fk_tab_master mst,
fk_tab_detail det,
drive_tab drv
Where mst.fk_refno = det.fk_refno
and drv.fk_det_refno = det.fk_det_refno (+)
I would say it was in improper use of an outer join. In this case -- if we find a row in DRV that doesn't have a match in DET and we "make up a row" for it -- the predicate "Where mst.fk_refno = det.fk_refno" will immediately drop it for us (nothing is ever equal to NULL). Hence the outer join in this example is only useful to negatively impact performance (it limits the plans available to the optimizer but its existence does not change the outcome ever).
You can drop the (+) in that query and you'll have exactly the same query!
the second query however, outer joins to the JOIN. As demonstrated above -- the predicate "Where mst.fk_refno = det.fk_refno" no longer drops the row from the result set since IT is done first and the result of that is outer joined to the other table.
See
</code>
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=41954.1 <code>
for the hash_area_size. It describes the process and how this parameter is used.