Re: UNION ALL PUSHED PREDICATE Question

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_at_uchicago.edu>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.'

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