How delete records when foreign key constraint is involved
Hi, I have 2 tables: A (with 3,000 records) and B (with 300,000,000 records). B has a foreign key constraint versus A.
If I execute "+delete from A where...+" it takes a long time to return (many hours), I think because Oracle checks integrity constraint from B to A via foreign key.
To reduce execution time of delete statement on A, I think this solution:
- first, I execute "+alter table B DISABLE constraint FK_VS_A+"
- then "+delete from A where...+"
- and in final "+alter table B enable novalidate constraint FK_VS_A+".
Is it correct ?
How many time requires re-enable constraint on B table with 300,000,000 records ? All actions must take few time as possible, so out of service is reduced.
I note that B table has been previously purged, so there are no records to point to deleted records in A table. Thanks for support. GP