Hello



I'm using the following sentence, where the Personas table has more than
19000 rows and the subquery returns, more or less, 5 rows.



select p.Apellido, p.Nombre

from Personas p

where p.Persona_ID in

(select b.Persona_ID

from Beneficiarios b

where b.Beneficiario_Titular_Id = :Titular_ID

)



The execution plan 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))



This execution plan kills the query performance.



I changed the query to

select p.Apellido, p.Nombre

from Personas p

where p.Persona_ID in

(5,100,500,80,30)

And the execution plan was:

Plan

PLAN (P INDEX (PERSONAS_PK,PERSONAS_PK,PERSONAS_PK,PERSONAS_PK,PERSONAS_PK))



Adapted Plan

PLAN (P INDEX (PERSONAS_PK,PERSONAS_PK,PERSONAS_PK,PERSONAS_PK,PERSONAS_PK))



I tried to force the execution plan, and wrote the following sentence

select p.Apellido, p.Nombre

from Personas p

where p.Persona_ID in

(select b.Persona_ID

from Beneficiarios b

where b.Beneficiario_Titular_Id = :Titular_ID

)

PLAN (P INDEX(PERSONAS_PK))



But I got an

index cannot be used in the specified plan

:

index PERSONAS_PK cannot be used in the specified plan



I think the problem is that the engine doesn't know how many rows the
subquery will return (neither me :-)).



I'm wondering if there is some way to force the execution plan to use the
index PERSONAS_PK?



Thanks in advance.

Mauricio Italo Magni



[Non-text portions of this message have been removed]