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: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 15 Jul 2003 18:46:10 -0700
Message-ID: <bdf69bdf.0307151746.5bea5644@posting.google.com>


kin_ng5_at_yahoo.com (Kin Ng) wrote in message news:<d5b3f600.0307121116.283114b1_at_posting.google.com>...
> 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?

One example may be subquery unnesting and view merging. If inner view has a predicate with rownum then it wouldn't be merged. Same for subqueries. Therefore, you'll get a different plan and different execution time.

In theory, however, a plan with query transformations applied should be more efficient than without. Not in practice, of course. Received on Tue Jul 15 2003 - 20:46:10 CDT

Original text of this message

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