RE: Unindexed FK Cause Deadlock or Only Share Lock?
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 16:06:49 -0500
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@xxxxxxxxxxx]
Sent: Thursday, June 30, 2005 3:59 PM
To: Post, Ethan; jonathan@xxxxxxxxxxxxxxxxxx; Oracle-L@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
