Best approach to archive a table of 6 billion rows

[pradyut.dhara]

Hi,
We have a table (MyTable) of 534315157 rows with a Clustered index of 3363756 KB.
This table is replicated to other databases (both warm stand by and oracle databases). On average 300K rows are insetred in this table in a day. Now we have to delete rows older than 2.5 years.After calculation it is found that total number of rows to be deleted are 267216286.
During production relase we have only 3 hours time (at max) to delete these rows after switching off the replication.After analysis we have following options:

• Extract all keys (Clustered index) from warm standby database. An nonclustered index is present on date column in warm standby database. Load all the keys into KEY_TABLE in main database. Then delete from MyTable in batch of 500/1000 rows joining KEY_TABLE & MyTable.
• Create a new table MyTable_New and insert the number of rows into MyTable_New. Truncate the old table MyTable and finally rename the new table MyTable_New to MyTable.

Since application is logging 300K rows on a day, number of rows to be inserted will be higher than number of rows to be deleted.

What will be best approach to delete 267216286 rows from MyTable considering the time?
Please feel free to add any new approach. We are going to test both these options and find out the statistics.

Thanks in advance for your suggestion.

[DerekAsirvadem]

Pradyut

(Sorry, I have been away.)

For both options, the configuration of the server, and the resource allocations for the database (or table0 concerned, is goin gto have a large impact on the speed, whether the tasks complete in 3 hours or not. So I recommend that you spend some time gearing up the server , and ensuring optimal performance.

Depending on how you perceive it, this is Option 3 or a variation of Option 2. For such a large percentage of DELETEs, I would:
• Create MyTable_New correctly
• SELECT *
____INTO EXISTING TABLE
____FROM MyTable
____WHERE retain-condition

SELECT INTO is much faster, as it logs Allocation Units only. You will need the db_option SELECT-INTO set, which will break the transaction log recovery cycle, but I am assuming you have to DUMP DATABASE anyway, after this task, and re-synch with the replicated dbs.

SELECT INTO now allows EXISTING TABLE. If you are on an older version which does not have it, remove the explicit CREATE TABLE, and create the target table the old-fashioned way, implicitly, within the SELECT-INTO. Make sure you provide a full and correct column list.

----

Option One. DELETEs are the slowest operation. If you consider that option seriously, I would take this approach. Forget about the 3-hour window. Treat it like a normal OLTP process, that will get replicated to the external dbs in the normal way, as per normal daytime constraints. Run the DELETE in the loop, with a batch size of 100 (you want really small transactions, which will get replicated without issue, and avoid interfering with normal daytime usage). Set the priority to LOW, so that it will not interfere with the normal business usage during the day. Allow it to run for hours or days.

Within the loop, when the rows deleted is a multiple of 1000, PRINT a progress message with timestamp to the output, which you can capture with -o in isql. That will allow you to watch the progress. You also need to SET FLUSHMESSAGE ON.