chee hee wrote:

> Hi All,
>
> I have a transaction table which has about 5 million records (and growing) and has a foreign key column to a table called Company. The Company table only has 3 records. I understand that a foreign key will have an index created automatically. So this would be a very low selective index and from the book theory, low selective index is not good for performance.
>
> So in general, should we create foreign key from very huge table (> million records) to very small table (< 10 records) ?
>
> Thanks in advance.
>
> regards,
> CM
>

IMHO the ideal sollution will be if we could *optionally* create a FK
constraint without an index at all, but this is not the current state of
FB, so the option will be to enforce RI constraint using triggers.

Triggers could not ptovide 100% accuracy on RI constraints because of
transaction isolation, but since these small tables (that are
problematic) tend to be "static" in practice you should not get in trouble.

FB 2.0 has a huge improvement on handling low selective indices.

see you !

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