Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: performance when inserting into child tables

RE: performance when inserting into child tables

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Wed, 21 Apr 2004 14:22:51 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5B52@exchsen0a1ma>


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



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Apr 21 2004 - 13:20:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US