Subject RE: [firebird-support] Why there are always one natural file in query
Author Helen Borrie
At 11:55 AM 9/09/2004 +1000, Alan McDonald wrote:

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

That would be true if there were no WHERE clause. "The entire set" that
ordering works on is the set that is output after the WHERE search.

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

It doesn't.
You are getting confused between searching and ordering, aren't you?

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

The entire set as specified, which (in a sane world) is not the entire
table. If someone specifies a set that returns a gazillion records, they
can expect it to be a bit on the slow side.