You need the indexes on the foreign key regardless of whether you are manually deleting the children or the children are being deleted using the cascade option.

Try adding the indexes and see if this improves performance.


-----Original Message-----
From: Rene Zhao
[mailto:oracledba-ezmlmshield-x98085941.[Email address protected]
Sent: Friday, July 30, 2004 12:04 PM
To: LazyDBA Discussion
Subject: Help - How to speed up DELETE with CASCADE?



Hello Gurus,

I have a parent table which has many child tables constrained on RUN_ID as the foreign key.
In FK creation I have the 'ON DELETE CASCADE' option, like the one below:

ALTER TABLE expense
ADD CONSTRAINT expense_run fk
FOREIGN KEY (RUN_ID)
REFERENCES RUN (RUN_ID)
ON DELETE CASCADE
/

The problem is when I try to delete certain RUN_ID from the parent table,
the query runs very slow (to finish all cascade deletion from its child tables).
Can any one tell me how to speed up the deletion when I have the 'ON DELETE CASCADE'?
My understanding is that if I didn't have 'ON DELETE CASCADE' then I need to delete
all the child records first, finally the parent, and it could be VERY slow
since Oracle in background would check all child tables to make sure there would be
no orphaned records before delete the RUN_ID from parent table - in that case I need
to add indexes on FK or to disable the FK constraint in order to speed up the deletion,
but what can I do to speed up the deletion in my case - 'ON DELETE CASCADE'?

I would appreciate any help on this!

We are running Oracle9i release 2 on Windows 2003 Server.
Currently I haven't put the indexes on the FK yet.

Rene


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page