At 05:19 PM 14/12/2004 +0000, you wrote:

> >
> > >I'm wondering if there is some way to force the execution plan to
>use the
> > >index PERSONAS_PK?
> >
> > 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.
> >
> > ./hb
>
>What would be the advantage of using a plan as opposed to subselects?

No answer to the question, since it's not a valid question. Plans are
prepared for all SELECT statements, including those embedded as subqueries.

>I personally use subselects, but some people here use plans and they
>are having a problem now with win 2003 server and firebird.

Ummmm, this is a non-sequitur. All Selects use plans. It's the job of the
optimizer to construct the best plan, given the indexes it has
available. You can see the optimizer's plan after the statement is prepared.

It's also possible to give the optimizer your own plan, as a PLAN clause in
the query statement itself (immediately preceding the ORDER BY clause, if
there is one, otherwise as the last clause in the statement). The easiest
way to do this is to take the optimizer's plan as a starting point and
modify it the way you think it ought to be. The optimizer still uses its
own plan for some internal calculations, but it won't try to override a
user-supplied plan.

In general, the optimizer knows what it's doing and returns the best plan
for the statement you give it. If you get a plan that you think is wrong,
it can mean one of two things: either the statement itself is not optimal,
or you have duplicate indexes in there. The optimizer is known to avoid an
index altogether when it encounters dup indexes - hence, since Firebird
creates its own mandatory indexes for keys (PK and FK) without regard any
existing indexes, it's not unusual to have optimizer problems on database
schemas that were imported from another RDBMS.

./heLen