At 12:54 AM 19/08/2004 +0000, you wrote:

>I have read and re-read past posts on highly selective indices... but
>I do not understand what to do with Foreign Key constraints.
>
> >From what I understand, when I set up a Foreign Key Constraint, an
>index is also created automatically.
>
>So for tables CustOrder, OrderStatus my constraint is set up
>from CustOrder.OrderStatus to OrderStatus.ID
>
>but the problem is that almost all CustOrders end up with a status
>of "Paid" and gstat reports are not good for FK_CustOrder_OrderStatus
>
>I understand that by creating a new index which includes the PK at
>the end (CustOrder.OrderStatus, CustOrder.ID) will help
>
>... but I do not understand what should I do with my original Foreign
>Key constraint???
>
>I do not want to delete it... as it is still a FK constraint!
>
>What am I missing here... or am I done, did I just need to add my
>highly selective index and that was all...??? and why isn't my query
>plan now using my new "highly selective" index?

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