> From: Jim Starkey
> >Anyway, what should the engine do to sort records now? Read them from their
> >data pages (sequentially or via indices), write them to the buffer, sort
> >the buffer, read it and finally transfer to the client side.
>
> It is generally (i.e. always) faster to make a sequential pass
> through selected records and sort the results than to bounce
> between the index and the data pages -- a quicksort, even with
> a merge, is faster than a page read.

Unfortunately "generally" does not always mean "always".

E.g. you have wide rows and ony one row fit on page
(perhaps not typical table, but good for this example);
then ordering by index means that you will have to read
index (only small amount of data), and then each data page
only once. On the other hand using sort files means (approximately)
that each row is read from datapage, written to sort file,
read from sort file, i.e. 3 times more i/o operations.

Now consider that rows are stored compressed on data page.
Uncompressed row from our example can be e.g. 5 times longer,
in which case you will need 11x more i/o operations
(because data are not stored compressed in sort files in current IB).

> The only time that ordering
> by the index wins is a) the metric is time to first record, or
> b) you aren't planning to look at any but the first few records.

Yes, but time has changed, so that interactive applications
are more common than simple batch processing;
it means that time to first record _is_ often important.
Retrieving only part of result set is also important,
especially with internet applications.


Ivan
prenosil@...