What are the options for deleting large amounts of rows from large tables?Answer: The answer to the best way to delete rows from an Oracle table is: It depends!Question: I have a very large table and I need to delete millions of rows from the table without the table fragmenting.I also need to use the best delete method, a best-practice way to delete large amounts of rows as fast as possible.This is a terrific feature that works for both SELECTs and DML, that can be configured without changing any SQL, and that does all the parallel processing within one transaction.Unfortunately built-in parallelism tends to increase resource consumption, not decrease it, and it interferes just as much with concurrent OLTP processing.
Current list of posts: Sometimes we have to do DML on many rows in a table, perhaps massive deletes or even more massive updates.Each chunk is processed separately as a separate transaction.We can process the chunks in parallel jobs or simply do one chunk at a time.I wrote pt-archiver for jobs like deleting or archiving rows from a big table in small chunks.These days, that’s the kind of task I like doing inside the database, and Shlomi’s magical common_schema feels a lot more suited for this than an external Perl script.