Re: Outer Join reads all rows from underlying tables

Matt,
Throw away is the difference between the number of rows scanned (using either a table or index scan) and the number of rows selected (after applying all the predicates). So large throwaway means that you had unnecessarily read many rows which failed to meet the criteria. Throwaway may be caused by full scans or unselective index range scans, missing indexes, missing predicate pushing, wrong join order .. You can see a lot of throw away in the view PS_TM_PEFF_V_CCEH.

From which table does tm_date come from ? It would have been better if you had included the view definitions of PS_TM_PEFF_V_CCEH, ps_tm_peff_gpqtwt, ps_tm_peff_v_cc_vw & PS_TM_PEFF_BMRKVW2.
Also what are the values for these parameters in your instance.
_complex_view_merging, _push_join_predicate, _push_join_union_view,
_pre_rewrite_push_pred.

There are several rules for View Merging. View Merging doesn't happen by default if the view contains GROUP BY or DISTINCT. Also the CBO cant merge views that are on the deficient side of the outer join if they have multiple base tables (which is your case for the view PS_TM_PEFF_V_CCEH). If you can give the view definitions, we can see why the predicates are not getting pushed.

If you are on 8i, you cant use DBMS_XPLAN but you can use event 10060 to get the predicate dump.

regards
Srivenu Received on Wed May 12 2004 - 03:01:33 CDT