Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange Oracle Optimization
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