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

"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

>
>
> "TomGreene" <c014572_at_yahoo.com> wrote in message
> news:7237e353.0311240741.10f5d7a8_at_posting.google.com...
> > I'm in an Oracle 8i environment and am at the mercy of a third-party
> > software tool that is generating SQL. The tool uses a view against
> > itself to get both detail and rolled up amounts. I can execute either
> > portion IV_1 or IV_2 separately in about 30 seconds. Combine them
> > (like here) and the time goes to 45 minutes. Since IV_1 returns about
> > 50 rows and IV_2 returns about 500 rows I'd like each view to fire
> > separately and then have the result sets joined. Is there a way to do
> > this?
> >
> >
> > SELECT *
> > 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;
> >
> > Thanks,
> > Tom
Received on Mon Nov 24 2003 - 13:17:49 CST