At 07:03 AM 19/08/2004 +0000, you wrote:

> > It *can* use that index, but the plan will still prefer the
> > existing single-column index, for no good reason. In some cases,
> > it won't choose either because it can't make up its mind either
> > way. You can force your own plan that replaces the FK index with
> > the one you prefer----provided the OrderStatus column is the
> > *first* key of the special index.
> >
> > Another way to look at this is whether you need that foreign key at
> > all. Do the Order Status "master" key values get changed and
> > deleted? Are you making the maintenance of control tables
> > available Uncle Tom Cobbley, Joe Bloggs and his dogs? Or do you
> > protect them so that only someone who knows the effects is allowed
> > to change or delete control values?
> >
> > /heLen
>
>I agree that there is usually little reason to allow users
>to "manage" control tables - so no, I do not allow any "joe" to start
>mucking around with OrderStatus values...
>
>However, RI is more than that... If I remove the RI (Foreign Key
>constraint) between CustOrder and OrderStatus, what prevents someone
>from adding a new CustOrder without a corresponding OrderStatus or an
>incorrect OrderStatus... the user is not editing the Control table
>but certainly our data entry people need to be able to add orders...

Why not use a CHECK validation technique instead?

alter table blah (
add constraint chk_valid_orderstatus
CHECK (OrderStatus in (select OrderStatus from OrderStatus)
or Orderstatus is null));

>That is where I am getting confused, am I supposed to remove my
>Foreign Key constraints, and add new indices, and then I can optimize
>my queries?

Nooooooo!!!! I wasn't suggesting you remove *all* of your FK constraints,
only these "lookup" style ones that cause badly-performing queries.

The point being made was that, if you want to force the use of your special
index, rather than the one chosen by the optimizer, then your choices are
to remove the FK (which in this case makes sense) or to use a custom plan.

/heLen