Until my DBA fixed the Explain Plan problem (getting Plan table not
setup error), I can tell you that when I changed the result to a
single row Count(*), I still get much faster result (12 seconds vs 43
seconds). If I break the query into 2 parts by storing the result
from the 1st part in a table and join the table with the 2nd part of
the query and the result is fast without the RowNum > 0 clause. We
are at 9.2 already so this isn't something old.
vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0307132322.6f372239_at_posting.google.com>...
> kin_ng5_at_yahoo.com (Kin Ng) wrote in message
>
> > I have a complex query that takes a really long time to run and
> > actually never completed because it ran out of space. But if I add
> > this meaningless (oh well, may be meaningful) condition in the
> > existing Where clause, the query returns data in a few seconds.
> >
> > and ROWNUM > 0
> >
> > Why adding this makes a query run so much faster?
>
> As Daniel said, _always_ check your SQL via explain plan to see how
> Oracle will be executing it.
>
> As for adding the ROWNUM clause. I think that you are seeing the
> difference between an ALL_ROWS and FIRST_ROW execution. BTW, the query
> may likely _seem_ faster when it is not. With FIRST_ROW it returns the
> 1st set of results pretty quickly.. and while you are
> looking/processing those, Oracle gets the next set, and next... etc.
>
> However, doing an explain plan will show what's happening better than
> my guestimates.
Received on Tue Jul 15 2003 - 00:53:34 CDT
