At 12:13 AM 9/28/2001 +0200, Moravecz Laszlo wrote:

>I thing, it's a bug, it's NO EFFECTIVE.
>
>PLAN SORT mean IB must read all row from table E before can sort the result
>! (and I can fetch the first !)
>If IB use E NATURAL in join, rows gets accidental order, cache can't extend
>P rows, IB must seek each in disk.

The problem is not is processing, but in retrieval. InterBase will
retrieve all the rows, building runs for the sort in temp space -
memory first, then files. When reading the rows InterBase doesn't use
a random order, but the order of the rows on disk, reducing the number
of reads necessary, improving cache utilization and so on.

This is not a theoretical issue - we've measured it. The time to the
last record is faster with a natural scan and a sort than walking the
table in index order. If you use a FIRST clause, in Firebird, then the
optimizer will try to use the index, assuming that you don't want the
whole result set.

>If IB use E INDEX ... in join, rows gets order of SQL command, if the first
>row joined, it can fetched immediately and if joined tables coming same
>order I can use an algorithm named 'interweave'. (for exaple two ordered
>file from magnetic tape to a third tape, it's old but good algorithm) These
>algorithm good for an intensive cache use too (for example if joined 1:N.,
>'N-1' times fired row in cache)

That algorithm is also called sort merge - we use it when two streams
can't be joined with an indexed loop.



Regards,

Ann
www.ibphoenix.com
We have answers.