---------- Original Message ----------- From: Dmitry Yemanov <[email protected]> > This is known as a nested loop join. And "restart from the root" costs > just a couple of page reads that are likely to be satisfied using the > cache. Not something really wasteful. > 2) Streams are read in the index order that allows to avoid an extra > sorting. But navigating the whole tables in the index order will cause > extremely random I/O (at least for the FK driven table) so it's also > going to be expensive. Firebird never chooses such an option at all. ------- End of Original Message -------
Thank you for the insight on performance considerations; it helps! select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num = bt_dchronexpl.filenumber order by bt_dchronexpl.FILENUMBER asc; --> PLAN JOIN (BT_DCHRONEXPL ORDER IX_DCHRONEXPL_FILENUM, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC)) I gather you're saying it won't use the index on the driving table in the absence of a compelling reason, but a group-by or order-by might cause it to do an indexed read, though that's unrelated to the join proper; it just avoids a final SORT in temporary space, but it still performs the join through a nested-loop algorithm. Two indices in a PLAN don't make a merge-sort. -Philip
