Re: Dumb Question regarding Indexes

Mark A wrote:
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > <snip>
> > The usual policy ought to be that all foreign key columns are indexed.
> --
> > Sybrand Bakker, Senior Oracle DBA
>
> Indexes on foreign keys may be a good thing, but saying that all foreign
> keys should be indexed can cause of lot indexes to be created that are never
> used.
>
> For example, assume that a division_code has only 3 unique values in an
> sales_transaction table, and the division_code is a foreign key to the
> division_table to insure that a valid division_code is always used. It is
> very unlikely that an single-column index on division_code would be used
> when accessing the sales_transaction table, but the overhead of maintaining
> the index exists for each row inserted.
>

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/

> The index on the division_code foreign key could be useful if the parent
> division_code table values were changed or deleted, but that is would likely
> be a rare situation, and not a scenario that one would want to optimize at
> the expense of inserting rows into the sales_transaction table.
>

And, if you read the above link you'll see the expense of NOT indexing is greater.

> I often find that by eliminating these kind of unnecessary indexes on
> foreign keys (usually automatically generated by modeling tools) that I can
> eliminate about half the indexes in a database, and not effect query
> performance at all (but greatly improving insert performance).

I'm glad you're not working with Oracle, as you'd be replacing them for performance reasons.

David Fitzjarrell Received on Fri Jul 29 2005 - 22:50:18 CDT