RE: performance when inserting into child tables
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Apr 2004 14:22:51 -0400
Jared,
you said:
"The lack of indexes for the FK's will cause a share lock to be taken
out on the child table when ever the parent is deleted or updated."
The most recent Oracle class I took said that in 9i, this lock on the child
table is taken and released immediately. Supposedly to speed the process up
a bit.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Jared.Still@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]
Sent: Wednesday, April 21, 2004 2:01 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: performance when inserting into child tables
I have a child table with a formal foreign key relationship to its parent.
I have noticed that in alot of cases inserting into them is much slower
than inserting into the parent.
I didn't design the system, but all of these cases do not have the foreign
key indexed. I think that is the problem, but I'm not sure why. If it
needs to scan the parent table to see if a value exists it can do an index
scan on the primary key?
Anyone know more about this?
------------
The lack of indexes for the FK's will cause a share lock to be taken
out on the child table when ever the parent is deleted or updated.
This means that updates, deletes and inserts into the child must
wait on the DML to the parent to complete.
Check your enqueue waits, they are likely somewhat excessive.
Jared
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
