Re: Indexes and Foreign Keys

Richard Foote wrote:

> 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

And one might point out that many people, Tom Kyte included, are strong advocates of IOTs. You'll not be dropping the primary key easily.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)