Hi Adam,

Adam wrote:

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

I share with you your perception that an FK should be declarative
instead of implemented by triggers.

I don't know how to make the optimzer choose another index based on the
same column :-( I think here will be a good point for a HINT clause....

I leave with a lot of duplicate indexes on my tables because of this.

I have a table that holds the company data, in some sites I have 5
records, in other just 1, and it have FK to almost all tables on my
database, so you could bet it will have a lot of duplicates (hundreds of
thousands) in some tables.

I know I am a purist on this aspect, but I like it that way, if I found
a place where it slows down considerably my queries and adding +0 to
avoid the index use could not help, I will revise it, but until now I
could live with it.

The case you provided should works equally in these two conditions:
1.) Adding +0 to avoid the index usage
2.) Have a way to declare a FK without the need of an index.

I don't know why you gain in speed if you set the plan manually to use
your "test" index, I know it should improve the garbage colletion
process, but the bitmap generated by the index scan should maps to all
(or almos all) data pages, so a natural read should perform equal. A bit
confused here based on your words that using the "test" index performs
better than no index at all. Are you sure that you don't see the "fast
first row" effect ? As I see it a full fetch should be equal (or faster)
then an indexed read that needs to read all data pages.

Sorry, no solution for you in my answer, but I really want to understand
what you are experiencing.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br