Re: Indexes and Foreign Keys

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message news:R6xRf.6169$dy4.4884_at_news-server.bigpond.net.au...
>
> 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
>

First, I certainly don't want to leave the impression that FK's should never be indexed. I only stated that not all FK's need to be indexed.

Secondly, I agree that your example about deleting rows where the attached FK has no rows in the dependent table is one case where a index is very useful, especially if it is done on a regular basis. That is what I said in my previous post.

But if you only rarely delete a row on a parent table that has no rows (or very few rows) on the dependent table, then I would not worry about it too much, even for a few million rows in the dependent table. Remember that you have pay a performance and resource price when inserting each of those millions of rows in the dependent table to have Oracle create the corresponding index row. Same will happen when the row is deleted. Received on Tue Mar 14 2006 - 13:20:59 CST