Ask the Expert

Problems deleting 600,000 rows from partitioned table

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.


    Requires Free Membership to View

There are many possiblities.

  1. 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.
  2. 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?
  3. 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.

    This was first published in October 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: