Deleting records from a table

[THoey]

We have an Oracle 7.3.4 database that has a table with over 8 million records in it, data back through Jan 1999. I have been tasked to remove all of the 1999 data. I didn't want to blow the rollback segments by deleting all the data at once, so I wrote twelve delete queries like the following:

DELETE FROM C_CONTACT
WHERE SUBSTR(DATE_OF_CONTACT,4,6) = 'JAN-99'

I am expecting this query to delete 138682 records, but it is taking a very long time (been going for over an hour now). I understand it has to do a full table scan to do this, but if I change the query to a SELECT COUNT(*) query, it takes about 10 minutes to count the same records.

Any suggestions on what I could do to speed this up? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[carp]

If you have an index on the date column, you could avoid the full table scan by using a where clause like

WHERE date_of_contact BETWEEN '1-JAN-99' AND '31-JAN-99'

(This assumes you have NLS_DATE_FORMAT set to 'DD-MON-YY' or 'DD-MON-RR'. If this is not the case you'll have to adjust the above to match your instance.).

Also, it may be the case that you have a child table that references the table you are deleting from. If your foreign key constraint includes the ON DELETE CASCADE option, you are also deleting rows from the child table(s) - which will increase your time (and rollback consumption).

 

[karluk]

It may be worth trying an index, but I'm not convinced that it will speed things up. With that many records to delete you may very well have changes in most of your blocks. My guess is that the combination of data block updates, updates to associated indexes, writes to the redo log, and rollback segment activity make the delete inherently slower than the select. An hour vs. 10 minutes doesn't seem that unreasonable.

Did your delete ever finish? How long did it take?

 

[karluk]

By the way, when you finish cleaning out your table, make sure you rebuild all the indexes that are defined on the table. From what I've read, Oracle can't reuse space after deletes from an index. If you don't rebuild, performance will suffer on queries that use an index.

 

[ljspop]

Hi,

Karluk is right - you will be wasting space in your indexes as well as having them be inefficient. Also, the high water mark for your table will be way off after a delete of a considerable percentage of your table. That means that full table scans will waste time scanning the empty blocks to the HWM. This can be fixed in 8i w/an alter table move statement, in previous versions I believe it requires an export/import operation to correct.

Probably not as big a deal as the indexes, but worth fixing as well.

Good luck,

Bob Bob Lowell
ljspop@yahoo.com

 

[THoey]

The delete never did complete. I left it running and had someone else look in on it, but they eventually just killed it off. I am going to take a look into getting it to complete, but it has been pushed back a step in priority.

Thanks for all the ideas, I will take a look at them when I pick this back up... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Farab]

The method I use to delete huge amounts of data with best performance is:
- Unload the data that you want to keep.
- Drop the table.
- Create the table/indexes.
- Load the data into the table.

This might seem like a strange way of doing it, but it's the fastest, because Oracle doesn't have to keep track of the rows being deleted.

The only drawback is you won't be able to do a rollback if you deleted rows you weren't supposed to, but that's what backups are for.