12.11.2014 16:14, Svein Erling Tysvær wrote:

>
> 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.

Does the plan change to INDEX if the condition would be:
1) ON p.icd10 = t.icd10
2) ON p.icd10 = t.icd10 OR p.icd10 = t.icd10
3) ON p.icd10 = substring(t.icd10 from 1 for 3)
?

> ICD10_PURRES is a tiny table, but I thought 112 records might be enough for an index to be useful

This expectation is basically correct.

> This is part of the following FOR SELECT:
>
> for with tmp (id_npr, avdnr, reshid, icd10, innskrivingsdato, aar, omsorgsniva, diagnosenr) as
> (select n.id_npr, n.tjenesteenhetkode, n.tjenesteenhetreshid,
> iif(substring(t.tilstand from 4 for 1) = '.', substring(t.tilstand from 1 for 3)||substring(t.tilstand from 5 for 1), substring(t.tilstand from 4 for 1)),
> n.innskrivingsdato, extract(year from n.innskrivingsdato), n.omsorgsniva, t.nr
> from npr n
> join npr_krg_id kid on n.npr_krg_id = kid.npr_krg_id
> join npr_tilstand t on n.ID_NPR = t.ID_NPR
> where kid.fnr_kryptert = :fnr_k
> and not n.innskrivingsdato between current_date-90 and current_date)
>
> select t.id_npr, t.avdnr, t.reshid, t.icd10, t.innskrivingsdato, t.aar, p.icd10_c, t.diagnosenr, t.omsorgsniva, g.gruppe, i.beskrivelse
> from tmp t
> join icd10_purres p on p.icd10 in (t.icd10, substring(t.icd10 from 1 for 3))
> join icd10_gruppe g on g.icd10 = p.icd10_c and not exists(select * from icd10_gruppe g2 where g2.icd10 = g.icd10 and g.gruppe < g2.gruppe)
> left join ICD10 i on t.icd10 = i.kode
> into ... do ...
>
> for which the optimizer creates this plan:
>
> PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10))

The OR condition prevents MERGE JOIN from being used, but I still think
that P INDEX (U_ICD10_PURRES_ICD10, U_ICD10_PURRES_ICD10) could be possible.

BTW, what FB version are we talking about?


Dmitry