To give a little historical perspective to this discussion (history Jim &
Ann are
unaware of as it happened during their "vacation" from InterBase).

When declaritive RI was added for version 4.0 - one of the performance
issues that
came out of testing was "Delete of primary key is slow". Reason: we had to
do a
scan of each FK relationship to ensure the PK value didn't exist. (this was
before ON UPDATE ... or ON DELETE clauses were supported). The engineer's
solution to the problem was to automatically create an index on the refering
column, to speed up deletes on the PK (oh, and Updates also would be sped
up).

Frankly, this was a "problem" that should have been fixed by saying "How
often
do you think deletes of PK values will occur" ? I think it is an
application
issue to put indices on the FK columns, if needed.

My gut reaction:
- Do not automatically create indices for FK's.
- SysAdmin should be allowed to disable / delete any index (even those used
for PK / FK relationships)
- Ditto for triggers (even system triggers - yes, RDB$TRIGGER4 should allow
it!)

Dave

-----Original Message-----
From: Ann Harrison [mailto:harrison@...]
Sent: Tuesday, April 04, 2000 12:53 PM
To: IB-Architect@egroups.com; IB-Architect@egroups.com
Subject: [IB-Architect] Foreign Key indexes


At 12:37 AM 4/4/00 -0400, Claudio Valderrama C. wrote:

> The main point is not the FK declaration itself (I will be
> surprised if the
>optimizer knew about it), it's the index that's created automagically as
>part of the FK that confuses the optimizer. Getting rid of that index makes
>the query run much faster.

Here are some questions about foreign key indexes.

Right now indexes are automatically created for every
foreign key. Those indexes can not be dropped and
require some backhand magic to rename. As a result, if
the 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.

1) The optimizer should be improved so that it doesn't
use really terrible indexes, right?

2) Does it make sense to maintain indexes that can never
be used?

3) Should InterBase just get out of the business of
creating indexes on foreign keys?

4) If InterBase creates indexes on foreign keys, should
it allow them to be dropped?

Cheers,

Ann



------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954874328/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com