Archives of the TeradataForum
Message Posted: Wed, 13 Nov 2002 @ 20:15:58 GMT
What we have found extremely effective is using empty destination tables for our insert/select, then using renaming to clean them up. When the final table has the same primary index as your largest source table (usually the case), and empty, transaction journaling does not occur, rollbacks are fast, and the process is extremely quick.
The scenario we have successfully implemented (in many cases) usually involves three identical tables.
Note: TABLE_DEST and TABLE_SOURCE must be in the same database.
TABLE_DEST is empty.
You may want to eliminate secondary indices on TABLE_DEST. Testing can show whether the presence of secondary indices will hurt you worse than rebuilding them.
Copy data to be modified from TABLE_SOURCE to TABLE_MODIFY.
Update data in TABLE_MODIFY.
Multistatement Insert select (Bteq or Macro) to combine TABLE_SOURCE and TABLE_MODIFY into TABLE_DEST.
INSERT INTO TABLE_DEST
Finally we rename tables
RENAME TABLE_SOURCE as TABLE_OLD;
Then create any needed secondary indices, collect stats and you are ready to go. We usually keep the old table around just long enough to validate that everything went well. A similar technique is used to delete old data.
I really wish that we could build all of this logic into a stopred procedure, but stored procedures do not support multi-statement requests.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|