Re: index usage on view union, with subselect

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<atpg31$ckd$1$8300dec7_at_news.demon.co.uk>...
> I like the 'nothing unusual there' line.
>
> Oracle has unnested a subquery, and
> recognised a partition view - that's a
> pretty good feat of optimisation.
>

you're right, of course. i guess i have come to expect magic from oracle.

> Do you have parameter
> partition_views_enabled = true ?
> Very recent versions of Oracle recognise
> partition views even when this parameter
> is set to false - I didn't think the behaviour
> went back to 8.1.7.0.
>

apparently, it does. mine is set to false.

> Oracle will automatically try to push joins
> into UNION ALL views and do partition
> elimination provided it recognises the UNION ALL
> view to be a partition view - which means that
> all the tables in the UNION ALL have exactly the
> same structural definitions. This means that
> all the tables must have the same columns,
> and types, in the same order, and all indexes
> must match across all tables - and the view must
> be equivalent to:
> select * from table1
> union all
> select * from table2
> ....
>

it's not so much that i want partition elimination, just a predicate push that will allow use of the indexes on the underlying tables. a range scan (+ table access by rowid) on both indexes would be fine. i was under the impression that this was achievable, even with a view of union (ALL or otherwise, but without identical tables). is there no way to get this to happen?

thanks for your answer.

>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
Received on Wed Dec 18 2002 - 11:35:52 CST