Hello Ann,

thank you for your very clear explanation.

We have to admit that the constructed example is over simplified. The productive statement, that is partially generated, is the following:

SELECT DISTINCT Artikel.nNummer FROM Artikel LEFT JOIN AuditsHorizontal
ON AuditsHorizontal.sKey = Artikel.nNummer AND (AuditsHorizontal.sScheme = 'Vorgabe_PBSeasyXML') AND (AuditsHorizontal.sTLC = 'Artikel')
WHERE
(( Artikel.nSortiment = 402087401 )
AND ( Artikel.cSprache = 'deu' )
AND ( Artikel.bGeloescht = 0 )
AND ((( AuditsHorizontal.dtModified <= Artikel.dtGeaendert ))
OR (( AuditsHorizontal.sScheme IS NULL )
AND ( AuditsHorizontal.sTLC IS NULL ) AND ( AuditsHorizontal.dtModified IS NULL ))
OR ( AuditsHorizontal.sAuditVersion != '01.00.003' )
)
)

And, as it can be seen, it has a where clause and, as it has been explained on this great list (!), therefore uses an index on the left table Artikel (= Article).

The plan, that is used, is

PLAN SORT (JOIN (ARTIKEL INDEX (ARTIKELEINGEFUEGT), AUDITSHORIZONTAL INDEX (CHECKFORMALKORREKT)))

And therefore Firebird 2.1.3 answers this query in 0.015 s for about 55000 rows in both tables, if it does not return an Article

We started from an exexution time of more than a hour and that makes optimizing a query - with the qualified help of all that have contributed to this on this list - an inspiring task. Thank you all!

Greetings

Andreas