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..
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.
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.
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?
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.
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.