Alan McDonald wrote:

>I have more than a few times wondered..
>why can't the optimiser use the order by clause to scan only the first n
>records of the index from the skip point? wouldn't it be a significant
>improvement to this select type to not have to first acquire the entire set?
>Why did we implement this first/skip this way rather than demand an order by
>to take advantage of huge speed efficiencies or just use natural for also
>ignore the entire set?
>Alan
>
>
>

Alan,

Perhaps I miss the point, but I think the engine should order the entire
result set to know wich records will be the first N.

What I think could be is the optimizer prefer an ORDER plan than a SORT,
because with ORDER (wich should be faster to retrieve the first records
in specified order) they will use the index to navigate trough the
records, then go to the data pages to see if it is visible to active
transactions and if so increment the number of "valid" records for this
fetch, when reach the N value stop searching, the same is valid for SKIP.

But as always someone will point where I have over simplified the whole
process :-)

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br