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

Oracle LazyDBA home page