Hi Adam!

Replying to both posts:

> 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).

Three brilliant reasons.

> 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.

Why? If your WHERE clause doesn't include TABLEB.ID, it cannot use
this part of the index anyway and if it does, then I'd say PK_TABLEB
is to be preferred.

> 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.

So far, I agree with the optimizer! But maybe there's something I do
not see yet.

> Even without the hint directive, can you think of any reason you
> would ever want to use a FK index with poor selectivity where a
> compound key starting with the FK field with perfect selectivity
> exists?

Well, I've not yet seen your SQL. A statement like

SELECT <whatever>
FROM TableB B
JOIN TableA A ON A.ID = B.TableAID
WHERE B.TableAID = :Param

may possibly use the plan you report, whereas

SELECT <whatever>
FROM TableB B
JOIN TableA A ON A.ID = B.TableAID
WHERE B.TableAID = :Param
AND B.ID = :Param2

would be better off with

PLAN JOIN (B INDEX (PK_TABLEB),A INDEX (PK_TABLEA))

In neither case do I believe the TEST index to be of any help. Please
show some SQL so that I get a better understanding of your problem and
a case where the TEST index is useful (I can understand the value of
adding the PK to an index when it comes to deletes, but not yet for
selects). Is it something like:

SELECT <whatever>
FROM TableB B
JOIN TableA A ON A.ID = B.TableAID
WHERE B.TableAID = :Param
AND B.ID BETWEEN :Param2 AND :Param3

If so, I may be able to understand the TEST index, though if so, I'm
surprised that the optimizer doesn't suggest it...

> Perhaps it is as simple as an improvement to the optimiser to ignore
> any index with a selectivity worse than ____ [insert value]. Then
> again, perhaps this is easier said then done.

You forget the case of unbalanced indexes. Suppose a cancer registry
containing only breast cancer. An index on gender would be useless
when looking for women, whereas it would be very good if looking for
men (breast cancer is far more common amongst women than amongst men).

Hmm, now I'm curious, tell us more,
Set