> I have an EXECUTE BLOCK statement, for which a small part goes NATURAL
> 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

What PLAN is generated for (SQL is not valid for you purposes but I want to see what the optimizer thinks)

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)
OR
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 potential
> records by 99% not enough to warrant the use of an index?

I don't see how the an index could be used to perform that reduction.

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.


Sean