Subject Re: [IB-Architect] Foreign Key indexes
Author Jim Starkey
At 04:43 AM 4/6/00 +0800, you wrote:
>>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.

No, that wasn't a consideration.

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

What do you mean by dense indexes? How do they solve the problem?

Jim Starkey