delete on db2 table

chulett · Post by **chulett** » Mon Mar 02, 2009 12:33 pm

You're deleting 30M out of how many total rows? May be more efficient to rebuild the table with just what you want to keep if the percentage is high enough.

bart12872 · Post by **bart12872** » Mon Mar 02, 2009 5:23 pm

chulett wrote:You're deleting 30M out of how many total rows? May be more efficient to rebuild the table with just what you want to keep if the percentage is high enough. ...

Well, the table contains 400M of rows and the most of the rows are real update (80%) insert (20%).

chulett · Post by **chulett** » Mon Mar 02, 2009 5:33 pm

OK, that question should always come up when deleting a large quantity of records and wanted to get it out of the way.

rajendharreddy · Post by **rajendharreddy** » Tue Mar 03, 2009 7:52 am

Hi,

Since delete is very costly statement, one option to solve the issue is,

1. Load the required data(remaining 370 M records) to a TEMP table.
2. Truncate (or) drop the main table. Following command can be used to truncate the table. This is very fastest method.

ALTER TABLE <SCHEMA.TABLENAME> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

3. Insert the TEMP table data to main table (or) Rename the TEMP table to main table.

Thanks..