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!
Unfortunately built-in parallelism tends to increase resource consumption, not decrease it, and it interferes just as much with concurrent OLTP processing.As I mentioned in my previous post, the basic idea is to divide a table into 12 “equal chunks”, each “chunk” being a rowid range that covers the same number of blocks (give or take 1). If our SQL is “taking too long” and we have unused resources, then we can take advantage of Oracle’s built-in parallelism. These processes can cause us problems such as: This last problem is more common than ever, now that many OLTP systems need to be constantly available.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.