Rick Debay wrote:

> Select statements generate multiple plans, if the select contains
> further selects.
> I need to specify my own plan for such a select statement, but I get an
> error on the second plan:
>
> SELECT
> *
> FROM
> FEE
> WHERE
> NOT EXISTS (SELECT 1 FROM FI WHERE FO = FEE.FUM);
> PLAN (FEE I_GOLD)
> PLAN (FI I_EGG) <-- Token unknown - char 1 PLAN
>
> Also, would an ORDER BY go after or before PLAN?

Hi Rick!

There's no way the statement you've written above could use an index for
FEE, it has to go NATURAL unless you have a fixed WHERE criterion on one
or more of the fields of the table.

Even though I love reading plans, I never remember exactly how they're
written. I do remember that it normally says PLAN SORT or PLAN JOIN, but
I never write plans directly in the query. It is generally better (and
in most cases possible) to hint to the optimizer trying to make it
understand which plan should be preferred rather than forcing it. If you
decide to force it, the plan should be at the end of the query, after
the ORDER BY.

To find out how to write a particular query plan, you could e.g.
download IB_SQL from www.ibobjects.com (freeware) write your statement
without the plan, and try to prepare it. IB_SQL will then show you a
plan and that plan can be copied into your statement. Most other tools
have similar possibilities, I'm just normally using IB_SQL myself to
check plans so that's what I used as an example.

HTH,
Set