> >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.

Maybe I am misunderstanding but when someone says "the entire set is
returned" before ording is achieved, it infers to me that the entire index
is scanned before a decision is made as to what record should be skipped to
and then what records returned in the FIRST part.
I would have thought it possible to scan only that part of the index as is
required to get to the skip point, then traverse the index (or sort plan
e.g. natural) the FIRST number of records, returning each as it goes.
On a large table, returning the records 10-15 in an indexed order should not
need the engine to traverse the entire million records.
And in my experience of using this "new" feature I do see a marked
improvement but Helen says the engine first returns the entire set and
discards the unwanted parts.

>
> 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

Maybe I am missing the implication of concurrent transactions here
Alan