Re: "limit 10" vs "rownum <=10"

Ed Prochak wrote:
> Gints Plivna wrote:
> > suppose you have
> > SELECT FROM <a very big table> ORDER BY <some columns> and fetch in
> > application only some first X rows
> > versus
> > SELECT * FROM (
> > SELECT FROM <a very big table> ORDER BY <some columns>
> > ) WHERE rownum <= X
> >
> > The second result you'll get many times faster than first one, because
> > Oracle knows that you'll need only first X rows and doesn't do all the
> > (waste of) work necessary to prepare for returning all rows (i.e.
> > reading all the rows in memory and doing monstrous sort).
>
> I really do not see how it can bypass the sort. Both done from a Pro*C
> (or similar) application?

Oracle cannot bypass scanning all the source. But it can bypass sortong all the result set. E.g. even if you have to do that on paper there is big difference if you have 1000 rows and need to sort all of them or just get 5 top rows. For 5 top rows you'll sort only first 5 rows, then scan all the source and immediately throw away anything that is greater than the last 5th value. If you need sort all the rows it will take much more time. This is just the case here. As usual all the credit to Tom Kyte, for detailed explanation look at his book Effective Oracle by Design page 501, Top-N Query Processing with ROWNUM.
One more note - according to this book it is valid starting from 9i.

Gints Plivna
http://www.gplivna.eu/ Received on Fri Aug 04 2006 - 15:26:53 CDT