| Subject | Re: [IB-Architect] Referential Integrity |
|---|---|
| Author | Jim Starkey |
| Post date | 2000-07-13T16:47:20Z |
At 05:46 AM 7/12/00 +0800, Joseph Alba wrote:
(though not on my shift) creates an index on the foreign key to
enforce the relationship during deletes from the primary table.
If the secondary table has 7 M entries distributed among 30
values, it has at least 7,999,970 duplicate values. A short
summary of the effect on performance is "disaster".
Ann has been chomping at the bit to include the record number as
an automatic minor component of the index key (including propogation
into the uppper index levels) to speed deletion from an index with
a gross number of duplicates. This will help.
An alternative solution (I think InterBase needs both) is a way to
indicate that a foreign key should be not checked on deletion from
the primary table to obviate the need to the index on the foreign
key.
is that somebody may want foreign key enforcement but not cascading
deletes. Maybe a new keyword is required.
Jim Starkey
>Dear BBW Jim,You have stumbled into an area chock full of elephant pits. InterBase
>
>I have a lookup table with an expected cardinality of around 30 rows
>I reference this table on a data table with an expected cardinality of 7
>million rows.
>
>
>Question:
>Is this safe to do?
>Would this establishing of referential integrity constraint not cause an
>index selectivity problem?
>
>-----
>
>Although this might look like a support question, what I'm really asking is,
>
>Is this a problem area (architecturally - establishing reference
>relationships) that needs a solution?
>
(though not on my shift) creates an index on the foreign key to
enforce the relationship during deletes from the primary table.
If the secondary table has 7 M entries distributed among 30
values, it has at least 7,999,970 duplicate values. A short
summary of the effect on performance is "disaster".
Ann has been chomping at the bit to include the record number as
an automatic minor component of the index key (including propogation
into the uppper index levels) to speed deletion from an index with
a gross number of duplicates. This will help.
An alternative solution (I think InterBase needs both) is a way to
indicate that a foreign key should be not checked on deletion from
the primary table to obviate the need to the index on the foreign
key.
>Interesting idea. Let me ponder it for awhile. The only problem
>I realize that an automatically created Index is quite useful when the
>nature of the reference is a Master-Detail one.
>But, if the nature of relationship is a Look-up (Lookup ID validity
>checking) the index on the referencing side is not necessary.
>
>Is this suggestion okay for the new IB? ->
>
>1. When the REFERENCE contains a CASCADE instruction -> automatically create
>the index because this is most probably a Master/Detail relationship.
>
>2. When the REREFERENCE does not contain a CASCADE instruction (restrictive)
>then, do not automatically create an index because this is most probably
>just a lookup relationship
>
is that somebody may want foreign key enforcement but not cascading
deletes. Maybe a new keyword is required.
Jim Starkey