Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 23 Mar 2005 @ 13:11:04 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Purging Data from tables
From:   Coffing Christopher L

The fastest way to do this (if you have the space to support this effort) is as follows:

Backing up the data before purge:

1. Create an Empty Table for the rows you wish to archive using a naming standard that defines the dated data in the table (ie: Jan2005_DateID).

2. Locking table databasename.tablename for Access Insert/Select the defined rows into the empty table where DATE_ID = '200501'.

3. Archive the table

4. DROP TABLE once archive is complete

Removing the Rows from the Table: (Here is where you need to determine if a straight delete or an insert/select is faster):

1. Create table databasename.tablename_TMP as databasename.tablename with no data;

2. Insert into databasename.tablename_TMP Select from databasename.tablename where NOT DATE_ID = '200501';

3. RENAME TABLE databasename.tablename to databasename.tablename_OLD;

4. RENAME TABLE databasename.tablename_TMP to databasename.tablename;

5. COLLECT STATS ON databasename.tablename (define stats);

See if this helps your effort.

Chris Coffing

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023