Richard Foote wrote:
> "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
I was ... thanks for the clarification.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Tue Mar 14 2006 - 10:52:54 CST
