Hi,

At August 11, 2004, 17:17, Evelyne Girard wrote:

> Hi,

> I'm trying to run an update on a table like this :

> update examen e set e.protocole='C'
> where e.id_visite in (select id_visite FROM Patient
> left join Visite on (Patient.ID_Patient = Visite.ID_Patient)
> WHERE Patient.ID_Patient = '45691')

> Statement Plan
> --------------
> PLAN JOIN (PATIENT INDEX (PK_PATIENT),VISITE INDEX (FK_VISITE_ID_PATIENT))
> PLAN (E NATURAL)

> But firebird doesn't use the index on examen.id_visite whereas this query
> does.

> update examen e
> set e.protocole = 'C'
> where e.id_visite in (8,10,10546)

> Statement Plan
> --------------
> PLAN (E INDEX (FK_EXAMEN_ID_VISITE,FK_EXAMEN_ID_VISITE,FK_EXAMEN_ID_VISITE))

> Is there a way to do this update with one SQL statement or must I execute
> the select and then execute the update for each row returned by my first
> query ??

It is as designed. Because the second WHERE clause is transformed
internally to: (e.id_visite=8) or (e.id_visite=10) or (e.id_visite=10546)
Which will use an index. And, with a subquery, it's hard to use an
index in the same manner.

But, if I'm not mistaken the IN clause with a subquery should be be
transformed to:

where exists(select 1 FROM Patient
left join Visite on (Patient.ID_Patient = Visite.ID_Patient)
WHERE (Patient.ID_Patient = '45691')
(Patient.id_visite=e.id_visite))

So what happens if you change the first WHERE clause to what I've
written above? Do you have an index on Patient.id_visite?

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)