I am trying to delete about 600,000 rows from a partitioned table. With a single delete SQL statement it ran for 24 hours when I finally killed it. Selecting all 600,000 rows takes less than a minute (full partition scan, I checked the execution plan). I tried looping through all the rows and deleting one at time in a PL/SQL cursor using the rowid. It deletes about 200,000 rows in a couple minutes, then slows down to one row every few seconds (I'm watching the SQL on v$sql table, rows processed, while the PL/SQL cursor delete runs). I tried committing every 10,000 deletes in the cursor, but it still slows down to a halt at around 200,000 rows.
There are no foreign key constraints to the table (I can truncate a partition just fine). The 600,000 rows are one day of data. I have it partitioned with 15 days in each partition, about 4.5 million rows of data per partition. What would cause it to slow to a halt at 200,000 deleted rows consistently like this?
Other option I've considered is to truncate the partition, then reload all 15 days of data. I tried it manually. The truncate partition is very fast (a few seconds), then I kicked off the load process for the 15 days, and it runs in about an hour (normal processing time for that many days). Seems like a lot of extra work when the delete for one day should take 10 minutes or less. Any ideas would be helpful.
There are many possiblities.
- Can you tell me where the indexes are stored for the tables? Try dropping any index on the table before deleting and recreate the index on the table after deleting the 600,000 rows.
- Make sure that the schema in which you are running the tables are analyzed regularly. Also, make sure you have enough space allocated per row for updates as you may have encountered row chaining?
- You may want to consult your DBA to ensure that the database is healthy and all parameter and operating system level space allocations are appropriate.