Painful cascade delete, Help!

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?

Thanks in advance!

George J. Xu
System Analyst
Information system
Northern Telecom North American Ltd. Received on Fri Dec 08 1995 - 00:00:00 CET