Re: Indexes and Foreign Keys

From: Mark A <nobody_at_nowhere.com>
Date: Sat, 11 Mar 2006 15:59:39 -0700
Message-ID: <0vidnQMdy8_Hy47ZRVn-iA@comcast.com>

"ianal Vista" <ianal_vista_at_hotmail.com> wrote in message news:Xns97838BAF5D450ianalvistahotmailcom_at_70.169.32.36...

> You should always create indexes for all foreign keys;
> otherwise the whole child must be locked when updating the parent table.

I would disagree that all FK's need indexes.

First, many PK's on parent tables never get updated, and they only get deleted if all the children are already gone. These are mostly small code tables.

Second, even if a PK on a parent table where to change, if the cardinality of the FK on the dependent (child) table is low, then an index would probably not be used to retrieve the child rows anyway. Indexes are usually selected by the optimizer when the number of blocks need to read can be reduced by using the index. But if there is always at least one row that qualifies for each value of the FK on each block, then it will be faster to do a table scan than to use an index. So in that case the index will not be used, and you are wasting system resources by defining the index and having Oracle maintain it. Received on Sat Mar 11 2006 - 16:59:39 CST