Hi

Have the following (cut down) ordered SQL

SELECT *
FROM Document d
LEFT JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef
ORDER BY dc.Description

There is an Index on dc.Description but it is not used. Plan shown below..

PLAN SORT (JOIN (D NATURAL, DC INDEX (DOCUMENTCREATOR_PK)))

555431 fetches, 0 marks, 2206 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 77830 index, 79193 seq.
Delta memory: 680 bytes.
Total execution time: 0.962s




If I make it an inner join instead the index is used and performance is
great...

SELECT *
FROM Document d
JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef
ORDER BY dc.Description

PLAN JOIN (DC ORDER DOCCREATOR_DESCRIPTION, D INDEX
(DOCUMENT_DOCCREATORREF))

527 fetches, 0 marks, 30 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 242 index, 0 seq.
Delta memory: -3141376 bytes.
Total execution time: 0.062s


Document table as about 80000 records and DocumentCreator has about 7000
records.

The DocumentCreator table is a lookup field. Documents don't have to
have a creator so it has to be a left join, most documents however do
have a creator.

What can I do with my SQL to 'encourage' Firebird to use this index?


Thanks
Rob