Re: Outer Join reads all rows from underlying tables

You have a lot of throw away in the plan. (especially in the view ps_tm_peff_v_cceh b)
>> Anyone know why the optimizer would retrieve all rows for each row
set
>> despite having a very restrictive WHERE clause..?
Which at is the best filter in your query ? If you are using 9i then explain the statement using explain plan for '--your sql--' and then give this sql to see the plan and the predicates in detail.
select * from table(dbms_xplan.display());

You have multiple levels of nested views. (i.e you have a view ps_rr_flat_vw
, which is based on other views, which in turn are based on other views).
It is better not to nest views down to more than 1 level.

It is also not good to reuse the views if they are not appropriate. Whenever you are using the view, see if you can use the underlying base tables directly.

regards
Srivenu Received on Mon May 10 2004 - 09:11:47 CDT