Re: Optimizer not pushing filters down into view

<mccmx_at_hotmail.com> wrote in message
news:1156243313.655389.260830_at_b28g2000cwb.googlegroups.com...
> Oracle 10.2.0.2 SE on W2K3.
>
> Is this a bug with the optimizer in 10g:
>
> I am seeing behaviour where constant filters are not being passed down
> into views in certain situations.....
>
> Q1. Query where filters are pushed successfully:
>
> select count(*) from PS_TM_PEFF_V_SHDAY
> where BUSINESS_UNIT = 'TMUK'
> and tm_shop_code = 'AA1'
> and tm_date = to_date('2006-07-28','YYYY-MM-DD');
>
> Q2. Query where filters are not pushed:
>
> select count(*) from PS_TM_PEFF_V_SHDAY A, PS_TM_PEFF_TWTCAL B
> where A.BUSINESS_UNIT = 'TMUK'
> and A.tm_shop_code = 'AA1'
> and A.tm_date = to_date('2006-07-28','YYYY-MM-DD')
> and A.BUSINESS_UNIT = B.BUSINESS_UNIT;
>
> The queries differ only in that I have added one extra table into the
> top level query.
>
> According to the tkprof and the autotrace output, the first query
> applies the filters on the base table contained in the view definition,
> whereas in the second query the filters are being applied much later -
> after the tables are joined. This is causing Oracle to join millions
> of rows unneccesarily.
>
> Its my understanding that 'constant' filters are always pushed into
> views but Join Predicates can be passed under certain circumstances.
>
> Any ideas why I amy be seeing this behaviour..
>
> I can post the view definitions and full plans if needed....
>
> Matt
>

As a wild guess, I'd go for the option
that Oracle is driving through the table PS_TM_PEFF_TWTCAL B - and
therefore has to do some joins before
it can filter on whatever tables those
filters apply to - but it would be a good idea to post the output from dbms_xplan() and view definition.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html