Re: Indexes and Foreign Keys

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1142268004.551122_at_yasure.drizzle.com...
> 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.
> --

Hi Daniel

I'm sorry, I don't understand what IOTs has got to do with the discussion ??

We're talking about deleting a parent key record, which you can do with IOTs just as easily as with heap tables, and the impact it could have on policing referential integrity on child tables if the FKs are not indexed.

You're not confusing deleting a PK record with dropping a PK constraint by any chance (which is entirely a different thing) ??

Cheers

Richard Received on Tue Mar 14 2006 - 04:41:41 CST