Re: Indexes and Foreign Keys

"Mark A" <nobody_at_nowhere.com> wrote in message news:0vidnQMdy8_Hy47ZRVn-iA_at_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.
>

Hi Mark,

Just on the issue of deleting a PK value.

I agree one would hopefully only delete a PK value if all the child rows are already gone (Oracle won't like it otherwise unless you cascade delete of course) but the question is how does Oracle confirm there are no matching FK values? Yes lookup parent tables are mostly small, but the child tables could be large, very very large.

And the only way for Oracle to confirm there's nothing in these potentially massive child tables that could violate a parent record being deleted is to use a suitable index on the FK column or perform an expensive full table scan.

A common problem I come across is not indexing those FKs where parent values are commonly deleted, especially when performance of the delete operation is at issue.

Cheers

Richard Received on Mon Mar 13 2006 - 04:38:30 CST