I am pretty sure this is a known Oracle bug. I think read something about it some time ago. On Wed, Aug 27, 2008 at 1:55 PM, Michael Schmitt <mschmitt@xxxxxxxxxxxx>wrote: > > > > > 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.3database). 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. > > > > > > > > > > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'