Page 1 of 1
Posted: Mon Mar 02, 2009 12:33 pm
by chulett
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.
Posted: Mon Mar 02, 2009 5:23 pm
by bart12872
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%).
Posted: Mon Mar 02, 2009 5:33 pm
by chulett
OK, that question should always come up when deleting a large quantity of records and wanted to get it out of the way.
Posted: Tue Mar 03, 2009 7:52 am
by rajendharreddy
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..