Check the timings...
INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP SELECT * FROM MATERIALS WHERE ACTIVE = 'N' AND MATNR BETWEEN '103' AND '105';
22,145 Records inserted in 1.1 seconds commit;
DELETE FROM MATERIALS WHERE ACTIVE = 'N' AND MATNR BETWEEN '103' AND '105';
22,145 rows deleted in 4.1 seconds
commit; and so on... by changing the ranges of MATNR so that, I roughly get about 20,000 records to INSERT into the dump table and DELETE from materials table. But after four such INSERTS and DELETES (the inserts taking on an average one second and the delete three seconds), the delete simply hung. The insert was done in 1.1 second. But the delete took more than two hours before I did a Ctrl C.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.