|Subject||RE: Why does the optimizer choose NATURAL for this tiny table?|
> I have an EXECUTE BLOCK statement, for which a small part goes NATURALWhat PLAN is generated for (SQL is not valid for you purposes but I want to see what the optimizer thinks)
> where I'd expect it to use an index associated with a UNIQUE CONSTRAINT.
> I would expect
> JOIN icd10_purres p ON p.icd10 IN (t.icd10, substring(t.icd10 from 1 for 3))
> to use an index, maybe something along the lines of:
> PLAN JOIN (P INDEX(U_ICD10_PURRES_ICD10, U_ICD10_PURRES_ICD10), ...
> However, the optimizer goes NATURAL.
> ICD10_PURRES is a tiny table, but I thought 112 records might be enough for
JOIN icd10_purres p ON p.icd10 = substring(t.icd10 from 1 for 3)
Or to create a new TMP column (t.icd3) which returns the substring(t.icd10 from 1 for 3) value and you change the JOIN to
JOIN icd10_purres p ON p.icd10 IN (t.icd10, t.icd3)
JOIN icd10_purres p ON ((p.icd10 = t.icd10) OR (p.icd10 = t.icd3))
> an index to be useful Or is the possibility of reducing the number of potentialI don't see how the an index could be used to perform that reduction.
> records by 99% not enough to warrant the use of an index?
Each T.icd10 value needs to be tested for each row of TMP, and each T.icd10 value is itself the result of a calculation.