Archives of the TeradataForum

Message Posted: Wed, 13 Nov 2002 @ 20:15:58 GMT

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

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.

TABLE_SOURCE
TABLE_MODIFY
TABLE_DEST

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
SELECT * FROM TABLE_SOURCE
WHERE primary key is not in
(SELECT primary key from TABLE_MODIFY)
;INSERT INTO TABLE_DEST
SELECT * FROM from TABLE_MODIFY;

Finally we rename tables

RENAME TABLE_SOURCE as TABLE_OLD;
RENAME TABLE_DEST AS TABLE_SOURCE;
RENAME TABLE_OLD AS TABLE_DEST;

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.

Glen

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