Re: Dumb Question regarding Indexes

<fitzjarrell_at_cox.net> wrote in message

> And in DB2 this may not be an issue, however with Oracle unindexed
> foreign keys create problems you can read about here:
>
> http://asktom.oracle.com/~tkyte/unindex/
>
> And, if you read the above link you'll see the expense of NOT indexing
> is greater.
>
> I'm glad you're not working with Oracle, as you'd be replacing them for
> performance reasons.
>
> David Fitzjarrell
>

I work with both Oracle and DB2. The article you cited in the link above reinforces exactly what I said about indexes on foreign keys (and when they are not necessary).

Let's quote one point at a time from the article and then compare it to what I said.

1. "The [issue] first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed."

Yes that is correct, but if the parent table is a code table, like division_code, it is not going to be updated or deleted on the parent code table except in very, very unusual circumstances.

2. "The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table."

I don't believe that anyone would define a FK constraint to a parent code table (like division_code) with a delete cascade rule. As already stated, it would be extremely rare that the division_code rows would be deleted or updated on the parent code table (maybe inserted if a new division is added).

3. "Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description)."

Yes, the glaring exception is the code table, as I have explained in excruciating detail. In my experience the indexes on FK's that relate to code tables (the glaring exception cited above) can represent about half the indexes in a database when the index on FK rule is strictly enforced. These indexes are usually never used in queries, and are just overhead for inserts and deletes (and occasionally updates) of rows on the child table.

Thank you for providing documentation for all the points I made. Received on Sat Jul 30 2005 - 00:21:04 CDT