Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dumb Question regarding Indexes

Re: Dumb Question regarding Indexes

From: Mark A <nobody_at_nowhere.com>
Date: Fri, 29 Jul 2005 12:08:21 -0600
Message-ID: <WrmdnYaugYwO7XffRVn-pw@comcast.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US