At 04:17 PM 11/08/2004 -0400, you 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)

These are not equivalent queries. The one with the subquery won't use the
index because of the left join, which allows nulls in the set. What is the
purpose of the left join here? Logically, it's going to select the
Patient.ID regardless of whether there is a match in Visite.

Also, you need to be quite specific with aliasing when you have subqueries,
so that the optimiser is not confused about which index to choose. See
whether correcting the aliasing makes any difference to the plan:

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

If it's Firebird, the optimiser will try to resolve your subquery to an
EXISTS() predicate. For Firebird, you could help the optimiser (and the
clarity of your code) by providing that yourself. For IB, you'll get a
much more efficient subquery.

update examen e
set e.protocole='C'
where EXISTS (
select 1 from Patient p
join Visite v on p.ID = v.ID_Patient
WHERE p.ID_Patient = '45691')

/heLen