Re: performance when inserting into child tables

From: <Jared.Still_at_radisys.com>
Date: Wed, 21 Apr 2004 11:01:25 -0700
Message-ID: <OFD221B721.4D9E097E-ON88256E7D.0062CA76-88256E7D.00630224@radisys.com>

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


-----------------------------------------------------------------

Received on Wed Apr 21 2004 - 12:58:56 CDT