I agree with Mr. Schnepper.

a) Do not automagically create FK indices

b) Allow for alter index on PK and FK indices

* My guess is that to be done correctly some
work will need to be done with Metadata caching/
loading code to avoid the bothersome 'Object
index in use error'.

c) Consider tying the uniqueness of an index
definition to the fields not the index name to
prevent the creation of duplicate. redundant
indexes.

(tangent -- sorry)
d) To add to Dave's Trigger request, I'd like to
see Procedures obtain an active\inactive switch.
If an interface to the database was built with
Procedures, having the ability to inactivate them
would allow one to shut down access to parts of
or all of the database while not taking it
offline. It might also help with some of the
dependency issues when altering triggers and
procedures.

Markus



David Schnepper wrote:

>
> 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
>
> ------------------------------------------------------------------------
> Save up to 54% on Quest & Kelty tents, backpacks, sleeping bags and
> outdoor gear. FREE Shipping and a 30 Day Money-Back Guarantee at
> screaminghotdeals.com
> http://click.egroups.com/1/2716/3/_/_/_/954879758/
> ------------------------------------------------------------------------
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com