Re: Outer Join reads all rows from underlying tables

Thanks for the response...

What do you mean by throw away....?

The most restrictive WHERE clause is the tm_date column, but it seems like its not applying any of the WHERE clauses to the view (retrieves all rows)

Can't use dbms_xplan.display, I'm on 8.1.7.

Do you think that the problem is due to the nested views...? If so I can atttempt to rewrite the query with direct access to the tables, or by using in-line views.

Thanks again

Matt

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0405100611.23ad2966_at_posting.google.com>...
> 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 Tue May 11 2004 - 03:01:17 CDT