Hi,
RUN_ID in the RUN table should be indexed if it is not already.
HTH.
-----Original Message-----
From: Rene Zhao
[mailto:oracledba-ezmlmshield-x98085941.[Email address protected]
Sent: Friday, July 30, 2004 1: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
The information contained in this e-mail message is confidential, may
be legally privileged, and is intended for use only by the individual(s)
named above. If you are not the intended recipient of this message, you
must not copy, forward, disclose or otherwise use it, or any part of
it, in any way whatsoever. If you have received this message in error,
please notify the sender immediately by return e-mail and delete the
message and any attachments from your system. Thank you
Oracle LazyDBA home page