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

or use the rownum pseudocolumn in one of the inline views - I believe one should be enough:

SELECT IV_1.*, IV_2.y1, IV_2.y2, IV_2.y3, IV_2.y4, IV_2.y5, IV_2.y6 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, rownum 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;

"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.
>
>
> "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 - 15:19:42 CST