It is because when you delete a record from a parent table, a cascading
delete will delete the child records. For that matter deleting the
child records directly leads to the same problem -- where are they?
With an index you know which exact location on disk the record is and
therefore only have to lock that record. Without one, you have to scan
the entire table and put a lock on all records because you are in the
middle of a delete, (update/insert) and you don't want changes going on
after you initiated the DML. With an index same thing, except that you
already know which records to lock, and don't have to scan for them.

-----Original Message-----
From: Kerber Andrew
[mailto:oracledba-ezmlmshield-x84328803.[Email address protected]
Sent: Thursday, March 31, 2005 8:04 AM
To: LazyDBA Discussion
Subject: RE: UnIndexed FKs

I have asked this before, and the only answer I get from Oracle is thats
the way it works. I believe it is a bug, but Oracle says its a
feature...

-----Original Message-----
From: kazi
[mailto:oracledba-ezmlmshield-x43876114.[Email address protected]
Sent: Thursday, March 31, 2005 06:10
To: LazyDBA Discussion
Subject: Re: UnIndexed FKs


In case of Un-indexed foriegn keys an delete from master table,
whole related table is locked. Why? I also want to nknow.

KAzi

fahd-m wrote:

>Hi,
> Why Un-indexed foriegn keys lead to deadlocks?
>
>Thanks,
>Fahd
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>
>
>
>



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page