>Actually, the problem isn't with FK_dettbl, but with the A Natural. In a 
>Master detail relationship with 450K+ rows in the master and 800K+ rows in the 
>detail, that "natural" is a killer.

The problem is that Firebird thinks that

PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl))))

is a better choice than 

PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl))))

The optimizer doesn't think NATURAL is better than using an index, it thinks 
that the selectivity of FK_dettbl is so much better than IXColDetSmIntFlag and 
PK_msttbl combined that it outweights the cost of going NATURAL on A.

Normally, it isn't all too bad to go natural on the first table of a plan, but 
b.ColDetSmIntFlag = 1 may be more selective than the optimizer thinks and then 
preventing a particular plan from being used  can help speed up queries like 
yours. The +0 should prevent FK_dettbl from being used, and I'm pretty sure the 
optimizer will not try to go natural on both tables. Hence the optimizer should 
suggest another plan - hopefully the one you want.

HTH,
Set

  • ... andrew_s_...@yahoo.com [firebird-support]
    • ... Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]
      • ... andrew_s_...@yahoo.com [firebird-support]
        • ... Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]
          • ... andrew_s_...@yahoo.com [firebird-support]
            • ... Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]
              • ... andrew_s_...@yahoo.com [firebird-support]
                • ... Ann Harrison aharri...@ibphoenix.com [firebird-support]
                • ... andrew_s_...@yahoo.com [firebird-support]
                • ... Tim Ward t...@telensa.com [firebird-support]
    • ... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
      • ... andrew_s_...@yahoo.com [firebird-support]

Reply via email to