----- Original Message -----
From: "Helen Borrie" <helebor@...>


> At 11:17 AM 22/03/2003 -0300, you wrote:
> >Hi,
> >
> >Is there any plans to solve the problem of low selectivity on indexes
> >created by foreign keys in FB?
>
> Foreign key indexes, per se, don't intrinsically have a problem of low
> selectivity. However, if you put a foreign key (or any type of index that
> is non-unique) on a column whose range of possible values is small, then
> you will get high distribution of few values, resulting in low
selectivity.

Yes, It's true.

> This is usually simple to solve, by making it a rule not to make
> single-column indices on such columns and not to make them foreign
> keys. The need for referential integrity on this type of key (which is
> usually a stable lookup table) usually can (and should) be "designed out"
> by adhering to the practice that you never allow client applications to
> change the referenced primary key; and that referenced rows in stable
> lookup tables are never deleted.

Change the primay key, I agree. But, delete this rows is necessary many
times.

> If you need ref. integrity on such keys,
> it's often a sign of a mistake in the data design. You do have the
option
> of writing your own RI triggers if you can't do without it.

I have tried to use RI by triggers, but the performance that I found is not
too good. With a small lookup table and a big main table referencing it,
deleting and updating columns on main table due changes on lookup table gets
a long time. The RI created using FK, as I have noted, is more efficient
doing this work. I don't know if this is really true. This is only what I
have noted.

> >Does this problem will be solved in FB 1.5 final release?
> A lot of optimization is going on. You need to ask this sort of thing in
> firebird-devel. It's not a support question.
I will do it when atkins site come back and allows posts.

Luiz.