"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.
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.
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).
Received on Fri Jul 29 2005 - 13:08:21 CDT
