Re: Indexes and Foreign Keys

"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.
>
> Cheers
>
> Richard

If the cardinality of the FK is high (lots of unique values) then having an index is usually a good idea. But if the cardinality is low (especially if fewer than 10) then it is usually just as fast for the cascade delete to do a table scan as it is to use the index to delete the rows. This is not unusual if the FK is associated to a parent "code" table, such as division code, country code, etc where the main purpose is to use the parent table to ensure the validity of the data on the FK.

Generally speaking an index will be used to satisfy an SQL statement if the number of blocks needed to be accessed can be reduced compared to a table scan. But if there are only 10 unique values of the FK, then there is probably at least one row on each block for any particular value. Obviously this assumes that the distribution of values in the FK is roughly the same (not highly skewed) and other factors such as the length of the row (which determines the average number of rows in a block). 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).

There are some other factors that might need to be considered such as how often such a cascade delete is performed, and concurrency considerations that could dictate using the index even if it is not faster than a table scan. Received on Mon Mar 13 2006 - 09:22:30 CST