Re: index usage on view union, with subselect

thanks. _push_join_union_view was the one that did the trick, but only when it was UNION ALL. i still thought that using the underyling indexes was possible with UNION, but i guess i was wrong. in any case, is the desire for this kind of behavior so uncommon that (in v8 at least) this parameter is false by default?

thanks again.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<atqhid$p6d$1$8302bc10_at_news.demon.co.uk>...
> There are a couple of hints and hidden
> parameters that may help - I can never
> remember details of which specific Oracle
> version they are relevant to:
>
> /*+ push_pred */
>
> pushes predicates into view.
>
> parameters:
> _PUSH_JOIN_PREDICATE = true
> _PUSH_JOIN_UNION_VIEW = true
>
> To set these with an alter session, you
> need to surround the parameter with
> double quote marks.
>
>
> --
> 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
>
>
>
>
>
> bung ho wrote in message
> <567a1b1.0212180935.24989585_at_posting.google.com>...
> >"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.
> >
Received on Wed Dec 18 2002 - 20:07:35 CST