>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                   [email protected]
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation