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 ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
