Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Jan 2011 @ 20:50:31 GMT


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


Subj:   Strictly Increasing Identity
 
From:   Michael Kappel

Is it guaranteed that the values of an Identity field -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) -- without recycling are strictly increasing? I am aware that sequential numbering gaps can occur as documented in the bulleted information below from a Teradata reference manual. Is it possible that an AMP on a later insert might use numbers previously allotted, but unused? For example, assume AMP1 is allotted 1 100 and AMP2 101 200, and IDs 1, 2, 3, 101, and 102 are set on the first insert. On a subsequent insert, would AMP1 ever start at 4 or is it allotted a new batch, say 201 300?

On a related note, I do not have control over the batch size (customer-driven) so I cannot set it to 1 to guarantee no gaps. I m assuming batch size is global for the instance and cannot be set to different values for different databases.

- Because the Teradata architecture is highly parallel, generated identity column numbers do not necessarily reflect the chronological order in which rows are inserted.

- Sequential numbering gaps can occur. Because the cached range of reserved numbers is not preserved across system restarts, exact increments cannot be guaranteed. For example, the identity column values for 1 000 rows inserted into a table with an INCREMENT BY value of 1 might not be numbered from 1 to 1 000 if a system restart occurs before the identity column number pool is exhausted.


Thanks,

Mike
Dr. Michael R. Kappel



     
  <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