Re: Strange Oracle Optimization

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.

--
Billy