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.

Other related posts: