From oracle-l-bounce@freelists.org Wed Apr 21 12:58:56 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3LHwuL18161 for ; Wed, 21 Apr 2004 12:58:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3LHwu618156 for ; Wed, 21 Apr 2004 12:58:56 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CE5DF72D31E; Wed, 21 Apr 2004 12:51:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18633-71; Wed, 21 Apr 2004 12:51:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A167272E148; Wed, 21 Apr 2004 12:51:15 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Apr 2004 12:50:03 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 722AE72D6BD for ; Wed, 21 Apr 2004 12:50:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18595-57 for ; Wed, 21 Apr 2004 12:50:02 -0500 (EST) Received: from mx10.radisys.com (mx10.radisys.com [206.102.10.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 08EF772D3B4 for ; Wed, 21 Apr 2004 12:50:02 -0500 (EST) Received: by mx10.radisys.com (Postfix, from userid 5) id 4951C142E04; Wed, 21 Apr 2004 11:01:38 -0700 (PDT) Received: from UNKNOWN(206.103.52.194), claiming to be "dcblackberry.radisys.com" via SMTP by mx10, id smtpdAAAWmmAs_; Wed Apr 21 11:01:27 2004 To: oracle-l@freelists.org Subject: Re: performance when inserting into child tables MIME-Version: 1.0 X-Mailer: Lotus Notes Release 5.0.10 March 22, 2002 Message-ID: From: Jared.Still@radisys.com Date: Wed, 21 Apr 2004 11:01:25 -0700 X-MIMETrack: Serialize by Router on DCBlackBerry/Radisys_Corporation/US(Release 6.0.3|September 26, 2003) at 04/21/2004 10:56:49 AM, Serialize complete at 04/21/2004 10:56:49 AM Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3461 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jared.Still@radisys.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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 -----------------------------------------------------------------