Archives of the TeradataForum

Message Posted: Fri, 11 Oct 2002 @ 13:49:33 GMT

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

You could also use a variation on a common technique that computes the surrogate key similar to the following:

Insert into cross_reference ...
Select ...
rank()
+ zeroifnull(max())
from staging ...

This assumes that the data is already populated in a staging table and one is maintaining a cross-reference table (of natural keys to surrogate key relationships). Also, the target table is populated by a following step that pulls in the related surrogate key.

This technique has several advantages, 1.) it ensures that each unique occurrence of the natural key columns will generate a unique surrogate key even when there are multiple occurences of the natural key, 2.) it does the assignment process on the box in parallel, 3.) does not leave any gaps in the sequence numbers, and 4.) you don't have to write/support an inmod and programmatically handle checkpoint/restartability.

However, while it does perform exceptionally well with moderate volumes of data it may not work well in your configuration with high volumes due to the fact that the data from the staging table must be redistributed.

Doug Drake

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