Hi Group,

After receiving some complaints at a particular site about slowness, I
decided to investigate and found that they have a foreign key index
with terrible selectivity.

Now I have tried a couple of things to improve performance. Obviously
by adding +0 in my joins and where clause where this field is
referenced, the speed problem is reduced, although it could certainly
do better if it had an index with good selectivity.

The structure looks something like this:

tableA
(
ID,
stuff
)

tableB
(
ID,
tableAID,
stuff
)

TableAID is a foreign key with terrible selectivity (for this customer
only 5 unique values for 90000 records).

Dropping the FK constraint is not an option because there is no way of
implementing a pseudo FK constraint using for example the CHECK clause
or triggers as this is not safe (isolation rules). It will also
confuse the reverse engineering tools we use from time to time, and
does go against the relational model (IMO).

I added the following index in an attempt to help the optimiser

CREATE INDEX TEST ON TABLEB (TABLEAID, ID);

however it is ignored. The index has a selectivity of 1, so I think it
should be favoured over another index with selectivity > 12000. But
instead, the optimiser elects

PLAN JOIN (TABLEB INDEX (FK_TABLEBTABLEAID),TABLEA INDEX (PK_TABLEA))

If I manually specify the plan

PLAN JOIN (TABLEB INDEX (TEST),TABLEA INDEX (PK_TABLEA))

it works very quickly. Is there anyway to suggest to the optimiser
that it should use the TEST index rather than the poor selectivity
foreign key, or do I have to put up with a table scan until the
optimiser is smartened up a bit more.

Also, is this improved in FB 2? I know the new index structure
provides less penalty especially for garbage collection where a long
chain of duplicates is involved, but will this also help here? Is
there a way in FB 2 to create a foreign key without it automatically
creating an index? Is there a way to tell the automatically generated
index to also include the PK field?

How do you deal with these scenarios in you applications if you can
not gamble on isolation allowing FKs to be broken (uncommitted deletes
from tableA are still valid for use in tableB if you try and write a
trigger to cope with it).

Thanks for any help

Adam