RE: Unindexed FK Cause Deadlock or Only Share Lock?

Sorry, I should have mentioned it is an even 50/50 distribution.

My point was you have a mechanism within Oracle which avoids any type of data inconsistencies while avoiding the deadlock by using the index. Not understanding the mechanics of this I have to ask if there should not be some way Oracle could "simulate" this type of operation without requiring the index? I tried to come up with an example in which the requirement to scan the index blocks would be a large operation just like scanning the table blocks.

This is probably the type of question I will "ask" Tom at Hotsos 2006 then stare back blankly and pretend I understand when he answers me.

My guess is that this could be coded somehow with oracle.exe but isn't really required since we should all be indexing FK's.

-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Thursday, June 30, 2005 3:59 PM
To: Post, Ethan; jonathan_at_jlcomp.demon.co.uk; Oracle-L_at_Freelists. Org (E-mail)
Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

Ethan, I don't think the cardinality makes any difference (somebody please correct me if I'm wrong). When you update/delete from the parent table of a FK relationship with no index on the FK in the child table, the *entire* child table is locked. Also, in your example, the index with 2 distinct values could prove to be very useful if they are unevenly distributed, for example if you have 10 "N"s and 1000000 "Y"s, the index would be very efficient for finding the "N"s.

Regards,
Brandon

--
http://www.freelists.org/webpage/oracle-l