Re: Delete lots of record

Ben,
When you need to tune DML statement (multi-row INSERT/UPDATE/DELETE), the first thing to do is to compare performance with equivalent SELECT, so you can find out how long it takes to IDENTIFY records. Then the difference between the time it takes to run the DELETE statement and time it takes to run SELECT gives you the actual "delete identified records" time. Compare these two times, in most cases one of them will be significantly longer, and start tuning from the longer one.

If SELECT part takes much longer, look at how you use indexes and what is in the EXPLAIN PLAN.

If DELETE part takes longer, that's probably because it needs to update indexes. In this case, check whether this DELETE can run at such time when no-one is accessing the table - it may be faster to drop indexes before DELETE and rebuild them after. If this is not possible, try to split this DELETE into several statements and run them in parallel.

Finally, if you are deleting most of the records (let's say, 90%), it may be faster to CREATE TABLE new AS SELECT with remaining 10% of the records, drop the original table, and rename new table.

Regards,
Sev Received on Wed Jan 30 2002 - 20:22:55 CST