|Subject||[firebird-support] Re: Why does the optimizer choose NATURAL for this tiny table?|
|Author||Svein Erling Tysvær|
>> D1) ON p.icd10 = t.icd10Oh, now you're surprising me, Dmitry! I thought it simply was the optimizer considering the two different plans to be approximately equally useful and that it chose one of them if comparing against one value, but the other when comparing against two (or more) values.
>> These change the plan to:
>> PLAN JOIN (MERGE (SORT (JOIN (P NATURAL, G INDEX
>> (I_ICD10_GRUPPE_ICD10))), SORT (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK),
>> T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)))), I
>> INDEX (PK_ICD10))
>Interesting. The only possible explanation is that the datatypes for
>P.ICD10 and T.ICD10 differ. ICD10 is an expression inside T, so its hard to say what the resulting data type is.
Both fields are ISO8859_1 (which is also the default character set of the database) with NO_NO as collation. It wouldn't surprise me if the collation got lost when doing IIF(COALESCE, ...), but I'm surprised if the character set changes to something that's neither the character set of the originating column nor the default character set of the database.
Thanks for suggestions from both of you, Dmitry & Sean! I was just looking for a simple answer that could increase my understanding, but take it now that the answer isn't all that simple. No need to look further into this now, after all, I haven't even finished my program that will run the procedure 500000 times and don't even know how quick or slow it will be yet.