> From: Dmitry Yemanov
> As far as I understand, the sorting mechanism should use an index to
> accomplish this task. The engine creates intermediate buffer irregardless
> of indices created for a table to be sorted.

No. It depends on the plan. If the plan is
PLAN SORT (...)
then IB will use either RAM or disk (depends on the volume of sorted data)
to perform sort. If the plan is
PLAN (tab ORDER idx)
then data pages are read in indexed order and result is sent directly
to client, without creating _whole_ result set in the file first.

> So when are indices used? If
> at the phase of the buffer creation, then why this buffer is needed at all?
> It seems to be simpler to read data pages directly from a database file(s)
> via these indices. Or am I wrong? The only reason I can find for this
> buffer is to sort records by non-indexed fields, but in this case I would
> store in the buffer and sort just a set of FIELD1, ..., FIELDn (from ORDER
> BY list) with appropriate DB_KEY or raw pointer.

Storing only sort-key-fields and db_key pointers in sort file
will have the same disadvantage as using index to order result set,
i.e. each data page would be read several times.

> 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. I do think
> there is too many I/O operations. I can easily imagine tens of records with
> blobs which are being sorted for a whole life.

Blobs are not part of data row (only blob id), so they are not
stored in sort file.

> To the best of my
> understanding, a time of sorting must depend on key fields size only (and,
> of course, record count), but not the entire record size. Is it *really*
> necessary to store in the buffer fields which are not used in sorting?

Ivan
prenosil@...