Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Auto increment in Teradata
 
From:   Doug Drake

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016