Re: CASCADE DELETE performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 25 Aug 1999 11:53:54 GMT
Message-ID: <37c6d8bd.2656019@newshost.us.oracle.com>

A copy of this was sent to "Ingo Wilhelm" <iwilhelm_at_taylormfgsys.com> (if that email address didn't require changing) On Tue, 24 Aug 1999 17:26:36 -0400, you wrote:

>Hi there,
>
>I have a database that contains tables that are nested up
>to four levels deep, i.e. the top-level table has dependents
>in a child table, which have dependents in another child
>table ... and so on.
>
>The tables are referenced with foreign keys using
>CASCADE DELETE, and each table has a primary key
>of one field and a unique index that contains several fields.
>
>The problem is that a DELETE of one or more records
>on the top-level table (causing cascade deletes on the
>dependent tables) is very slow.
>
>Are there any tricks to speed-up CASCADE DELETEs or
>DELETEs in general? I'm not a database expert but I
>think the reason for the sluggish delete is that Oracle
>has to maintain the indices during the deletes... If
>that is the case: can I get around it without affecting
>other users that are trying to access the tables?
>
>Thanks in advance ...
>
> Ingo
>
>

do you have the foreign keys in the child tables indexed? if not 2 things will result:

• in the case of an update of the parents primary key or delete of a parent record, the child table will be locked (the TABLE will be locked)
• a full scan of the child table will result for every row whose primary key is updated or deleted in the parent table.

I'll bet some of your child tables have unindexed foreign keys. You can use the following sqlplus script to find them (if all of the tables are in the same schema).... Any row with **** in the report has an unindexed foreign key in a child table

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,

		 substr(a.constraint_name,1,30) constraint_name, 
	     max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,

	     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,16,', '||substr(column_name,1,30),NULL))

columns

    from user_ind_columns
   group by substr(table_name,1,30), substr(index_name,1,30) ) b where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 25 1999 - 06:53:54 CDT