>i fthe parent table is small and the child table large, the
>foreign key index has very poor selectivity and can have
>a terrible effect on performance.
>

If you come to think about it, it is not the foreign key index's fault
really. Rather it is the selectivity issue that comes to play. And I noticed
most of the time, foreign keys do have selectivity issues.

There is a selectivity issue because IB uses only one kind of index
structure isn't it - sparse (which will have this kind of Achilles heel
whenever the key it indexes on is not unique and are too many to traverse
for comfort).

And there is only one kind of index because IB had that label of being
embedded, so it had to be kept small.

Would it not be nicer to have not only one but maybe two index structures?

On keys that are pretty unique, the designer can use the original index
structure. (sparse)
But for keys that have too many duplicates - thus designer can choose the
alternative (new) index structure (dense).

The result is, NO SELECTIVITY ISSUES for IB.

Joseph Alba
jalba@...