Re: Painful cascade delete, Help!

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

-- 
* disclaimer - My views respresent NT/BNR in every way, NOT!
NOTE - my real email address is bartok_at_bnr.ca, NOT vcross_at_bnr.ca
* non-work related email to vlcross_at_aol.com (Having an AOL account doesn't
  make me a complete idiot. Excessive drinking makes me one.)