Hello Helen

>
> You don't say what database engine you are using. Firebird should
> optimize
> this statement to the following (which does allow use of the index for the
> existential search):

I'm using Firebird Version 1.5.1.4481

> select p.Apellido, p.Nombre
> from Personas p
> where EXISTS(
> SELECT select 1 from Beneficiarios b
> WHERE p.Persona_id = b.Persona_id
> and b.Beneficiario_Titular_Id = :Titular_ID
> )
>
> Try this query and compare it with your original - especially if you are
> using InterBase. If you need more, come back and tell us what database
> server you are using.

The plan used for this query is:

Plan
PLAN (P NATURAL)
PLAN (B INDEX (BENEFICIARIOS_PERSONA_FK,BENEFICIARIOS_TITULAR_I))

Adapted Plan
PLAN (P NATURAL) PLAN (B INDEX
(BENEFICIARIOS_PERSONA_FK,BENEFICIARIOS_TITULAR_I))

I think that this plan is correct for the query, but not is what I want.

I'm using "select ... where persona_id in (select ...)", because what I want
to achieve is that firebird bring just few records (less than 10) from table
"Personas" (this table has almost 20K records).
If I use "select ... where exists (select 1 ...)", this is a correlated
query, and if there is no another restriction the engine always has to do a
"NATURAL" access over table "Personas".

Is there a way to make the engine work with "in" clause like a join?

> ./hb

Mauricio.