CODEWITHSUNDEEP
sqlauto-incrementteradata
Ajim Bagwan
Ajim Bagwan

Reputation: 137

Teradata auto-increment column taking different ranges as starting point while inserting records at different time periods

We have a table with an auto-increment column defined as follows:

 Col1 BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1 
        INCREMENT BY 1 
        MINVALUE -999999999999999999 
        MAXVALUE 999999999999999999 
        NO CYCLE),

Now, when we inserted 49 records for the first time, it took 100001 to 100049 as values for this Col1, instead of starting at 1. For insertion at the next hour, it took values 200001 to 200049. And so on. After the 9th hour it took 1000001 to 1000049 values.

How exactly does auto-increment in Teradta work? Is it being shared in memory with other auto-increment columns from other tables?

Why didn't it start with 1 and kept incrementing by one for the next insertions? How to fix this?

Upvotes: 0

Views: 1903

Answers (1)

dnoeth
dnoeth

Reputation: 60472

An IDENTITY is not a single sequence, this would be a bottleneck in a parallel database system. In fact for each table there are multiple sequences in parallel, one for each Parsing Engine and each AMP, each reserves a batch of values (default 100,000).

When you submit single row INSERTs through a single session, all rows are processed by the same PE, thus vales are assigned in sequence. When you logoff and logon again (or use multiple session) you might be connected to another PE with a different range of values.

When you INSERT SELECT row the sequence value is assigned by the source AMP, again each has a different range of values.

When TD crashes or is shutdown, all unused values are lost (similar to other DBMSes).

In your case you probably logged off in-between and logged on to a different PE.

Upvotes: 2

Related Questions