Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange Oracle Optimization

Re: Strange Oracle Optimization

From: Kin Ng <kin_ng5_at_yahoo.com>
Date: 14 Jul 2003 22:53:34 -0700
Message-ID: <d5b3f600.0307142153.5cbbc155@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US