Re: Painful cascade delete, Help!

Vince Cross wrote:
>
> In article <4aafai$3kf_at_nbdchc4.bnr.ca>, George Xu <George_Xu_at_Nt.Com> wrote:
> >Hi,
> >
> >I designed a base with about 50 tables. There is one master table and
> >about 40 child tables in which there is a referential key pointed to the
> >master table. The cascade delete constraint is enforced to assure data
> >integrity. when the number of records in the master table reach 30 K
> >and the number of records in the child tables reach 200k ( total is
> >300k), it takes 5 - 10 minutes to delete just one record in the master
> >table ( cascade deleted records are about 200 ). I delete 1500 records
> >in the master table which takes me three hours, It is painful! I know
> >if I disable the cascade delete constraint in the base, replace the
> >constraint with stored trigger (before or after delete on the master
> >table which is to delete child tables first, then delete master table).
> >the whole process will be much faster. But... I'd like to keep the
> >internal cascade delete constraint. Is there a way to resolve the
> >problem, like to tell ORACLE engineer to optimze smartly?
> >
>
> You could try creating indexes on the referential keys in the child tables.
> As for your idea of using a trigger, depending on how you do it you may have to
> work around a "mutating table" issue which usually means disabling your
> foreign key constraints that reference the master table and enforcing them
> thru triggers on each child. With 40 child tables, that could be nasty.
> Experiment with the indexes and you will probably see some improvement.
>
> Vince

Trigger implementation will work faster than cascade delete, for repeated delete operations on the master table, as the trigger sql statement will be cached for subsequent deletes. With cascade delete, Oracle must prepare the sql statement from data dictionary.

Shashi Received on Tue Dec 12 1995 - 00:00:00 CET