Re: How do I force two inline views to execute separately?

Both versions of your code ought to do
the job. (Which version of Oracle is this ?) There are no restrictions on the no_merge hint. It is a directive that forces a view (even an in-line view) to be instantiated as a physical result set.

Can you check the execution plan to see
if you are getting a VIEW line for each of the two 'no_merge'd views. If you are, then perhaps the optimizer is managing to come up with a nested loop join between them
instead of something more sensible like
a merge join or hash join.

"TomGreene" <c014572_at_yahoo.com> wrote in message news:7237e353.0311241117.4b8363a_at_posting.google.com...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:<bpt9fh$ep2$1$830fa795_at_news.demon.co.uk>...
> > Put the /*+ no_merge */ hint just after the select
> > in both the in-line views.
>
> Sorry, still bad performance with:
>
> SELECT *
> FROM
> (select /*+ NO_MERGE */ x1, x2, x3, sum(x4) x4 from BIG_VIEW
> group by x1,x2,x3
> where x5=1 and x6=2) IV_1
> ,(select /*+ NO_MERGE */ y1, y2, y3, y4, y5, y6 from BIG_VIEW
> where x5=1 and x6=2) IV_2
> WHERE IV_1.x1 = IV_2.y1
> AND IV_1.x2 = IV_2.y2;
>
> or with
>
> SELECT /*+ NO_MERGE (IV_1 IV_2) */ *
> FROM
> (select x1, x2, x3, sum(x4) x4 from BIG_VIEW
> group by x1,x2,x3
> where x5=1 and x6=2) IV_1
> ,(select y1, y2, y3, y4, y5, y6 from BIG_VIEW
> where x5=1 and x6=2) IV_2
> WHERE IV_1.x1 = IV_2.y1
> AND IV_1.x2 = IV_2.y2;
>
> I remember trying this a while ago. One thing, are there restrictions
> with the "no_merge" hint? BIG_VIEW referenced here is a view
> containing 6 tables and two inline views (each a 3-way union all)
> (please, no thrashings, I didn't write it, but that's what they all
> say!) BIG_VIEW also has some INDEX hints contained in the view.
>
> Thanks,
> Tom
>
Received on Mon Nov 24 2003 - 13:58:39 CST