All Forums Database
MattB86 2 posts Joined 10/15
11 Apr 2016
Identity column question - increment behaviour

I'm creating a table with the following column..
COLUMN1 BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 300000000000 INCREMENT BY 1 MINVALUE 300000000000 MAXVALUE 399999999999 NO CYCLE)
..which i'm then loading into, sometimes with an real value to be retained, otherwise DEFAULT(COLUMN1) to create an ID starting at 300000000000.
What I wasn't expecting is that after loading, the values don't strictly increment by 1 - for example I have 30000000000, 300000000001, 300000000002 then 300000100000, 300000100001, 300000100002 then 300000200000, 300000200001, 300000200002..
I don't think it's a problem as all the values retain uniqueness, I'm just not sure what the cause of this behaviour is - perhaps use of BIGINT and a large starting number? Or perhaps due to the 'GENERATED BY DEFAULT' syntax as I don't think this was happening when I used 'ALWAYS' syntax instead..
Thanks

11 Apr 2016

That's the limitation of Identity column.after a sequence it will produce random numbers,for getting a sequence better go with a row_number function or a window function

StevenSchmid 33 posts Joined 07/11
11 Apr 2016

Each AMP is assigned a pool of numbers in the range you specify, therefore depending on which AMP the row is written to, determines which numbers are actually used. 

Steven Schmid
Teradata DBA
Canberra, Australia

AtardecerR0j0 71 posts Joined 09/12
12 Apr 2016

StevenSchmid is right. Teradata works as a parallel system. To get strictly increment by 1 the work of the diferent AMPs should be sequential (not parallel), this would go against Teradata philosofy so each AMP is assigned a range of numbers to keep parallel working.

Be More!!

MattB86 2 posts Joined 10/15
12 Apr 2016

Makes sense - thanks for clearing that up.. It's funny as I'm pretty sure when using ALWAYS GENERATED, it was consistently incrementing by 1 which would suggest the numbers aren't spread across AMPs and thus is a less efficient design using that methodology?

AtardecerR0j0 71 posts Joined 09/12
12 Apr 2016

ALWAYS GENERATED-> If you insert a new row you get a new value for that column. It does not matter if  you specify that column or no. 
GENERATED BY DEFAULT-> If you want you can insert your own value for that column so Teradata only generate a new value for that column when you don't specify a new value for that column.

Be More!!

Fred 1096 posts Joined 08/04
12 Apr 2016

For single-row INSERTs, the PE obtains the next identity column value. For load or INSERT/SELECT, which potentially can involve many rows, the AMPs each obtain a "batch" of numbers as described above.

You must sign in to leave a comment.