Optimizer not pushing filters down into view

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 Received on Tue Aug 22 2006 - 05:41:53 CDT