Re: Indexes and Foreign Keys

"Mark A" <nobody_at_nowhere.com> wrote in message news:fLKdnXWRQqWuE4jZRVn-vw_at_comcast.com...
> "Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message
> news:GYbRf.5391$dy4.2655_at_news-server.bigpond.net.au...
>>
>> 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.
>>

<snip>

> If you are often deleting parent rows when all the matching child rows
> have already been deleted, then having an index is a good idea because the
> data would be highly skewed in that situation (zero rows for that
> particular value).
>

Hi Mark,

And your comment above is precisely what I was referring to.

If there are no child records and the performance of the delete on the parent table is important/an issue, then having an index on the FKs could be highly desirable to avoid expensive full table scans on the child tables.

I just wanted to clarify your earlier comment when you said "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." as a reason for not creating an index on the FK. The fact that codes tables are small and that you only delete if all the children are gone is still potentially a valid scenario for wanting your FKs indexed.

You may only have a handful of parent records but many millions of rows in related child tables and if you want to delete a parent record with no matching child records (and the performance of these deletes are important), then the delete will crawl along unless the FK's are indexed.

Cheers

Richard Received on Tue Mar 14 2006 - 04:42:57 CST