petek, 03. november 2006

DB2: Fast Delete Large Tables

Have you ever wanted to delete a table with millions of rows? Using
DELETE FROM TABLE1 SQL statement? Then you know this is very, very slow and it fills transaction logs. The fastest delete methond I've ever seen goes little like this:
1.) First you export 0 rows from the table:
EXPORT TO TABLE_NAME.IXF OF IXF SELECT * FROM TABLE_NAME WHERE 1 = 2
2.) Then you do a LOAD with REPLACE:
LOAD FROM TABLE_NAME.IXF OF IXF REPLACE INTO TABLE_NAME

I've been able to delete rows from tables with tens of millions of rows in a second.
I hope this little tip is usefull for a lot of DBA's out there.
Objavite komentar