UNION ALL PUSHED PREDICATE Question

Hi All,

I had a question about "UNION ALL PUSHED PREDICATE", and I was hoping someone could point me in the right direction (this is for a 10.2.0.3 database). We have a query that does not push the predicate into a view if we have a local bitmap index on a partitioned table sitting behind the view. If we drop the local bitmap index on one of the columns, then we see the UNION ALL PUSHED PREDICATE, the query uses partition pruning, and the query returns in a fraction of the time. I am trying to find out how the bitmap index is affecting the pushed predicate and the optimizer plan taken. The partitioned table sits behind a view and does a UNION ALL onto itself a couple of times.

An example of the view is as follows.

Create view v_TEST (...) as
Select x,y,z from t_TEST where col_1 is not null <-- bitmap is for this column UNION ALL
Select u,t,v from t_TEST where col_2 in (.......) UNION ALL
Select u,m,p from t_TEST where col_3 in (.......)

And the query looks like this

Select v_test.col_X from v_test, t_2
Where t_2.col_4 = v_test.col4
And t_2.col_1 = '200805'

The column with the local bitmap index is col_1 from the view above. I have gathered 10053 traces, but I am not familiar with sorting through that data and have not found what I have been looking for. When the bitmap index exists on col_1, the optimizer scans all of the partitions of t_TEST and chooses a plan that costs 80K. When the index does not exist (and we see partition pruning of t_TEST), then the optimizer chooses a plan that costs 6k and completes in a fraction of the time.

What I am trying to figure out is why the optimizer does not push the predicate into the view (and use partition pruning) when the bitmap index exists on t_TEST.col. Any pointers would be appreciated

Thanks in advance, and sorry if I did not provide all the needed information.

--
http://www.freelists.org/webpage/oracle-l