| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: performance when inserting into child tables
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_at_radisys.com [mailto:Jared.Still_at_radisys.com]
Sent: Wednesday, April 21, 2004 2:01 PM
To: oracle-l_at_freelists.org
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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |