Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Database
Enthusiast

Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Experts,

My Requirement is to generate the Sequential numbers in Column ..

As It can be done in two ways :

A) define as identity column 

b) CSUM(1,1) 

but i got to know from one of my peer that CSUM has some drawbacks like it locks the table. and Identity column has some constraint of upper limit ..  

As i am not sure ,Can any one suggest which would be more good in terms of performance?

Thanks!

Cheers,

Nishant Bhardwaj

14 REPLIES
Senior Apprentice

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Nishant,

you have to define "sequential" first, do those value have to be:

- really sequential (based on the position in the input file or some other order)

- without any gaps?

- or just unique?

IDENTITY will be be "just unique".

CSUM(1,1) should never be used, because it skewes to a single AMP (replace it with a ROW_NUMBER)

Dieter

Teradata Employee

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Depends on the questions asked by Dieter.

Based on that, you can use:

IDENTITY

SUM(1) OVER(ROWS UNBOUNDED PRECEDING)

ROW_NUMBER

RANK

....

Enthusiast

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Dieter and Adeel,

My REquirement is to get the Sequential with out any gaps..

does Identity column gives any gaps in between?

which one is prefered for with out gaps?

Cheers

Nishant

Senior Apprentice

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Nishant,

any IDENTITY/system-maintained sequence may result in gaps (due to restarts or rollbacks), but in Teradata it's not one sequence it's multiple parallel sequences, thus there are always gaps.

Without gaps is only possible with ROW_NUMBER plus

- a table maintaining the highest used value used in a transaction, in best case in a SP

- or ROW_NUMBER + (SELECT COALESCE(MAX(value),0) FROM tab)

Dieter

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

CSUM(1,1) should never be used, because it skewes to a single AMP

Hi Dieter,

Can you please explain more about this??

Thanks,

Dheem

Enthusiast

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Dieter,

I've always been told to steer clear of the IDENTITY in the past because it has some limitations in the past.  Such as the gaps in numbers and the limit to the number of values that could be incremented.  Meaning, it would run out of numbers and begin to wrap around and start over, using numbers that may have already been used in the past.  Do you know if these limitations have been corrected by 13.10?  We are considering using this IDENTITY feautre as we just need a unique value for a surragote key and do not care if there are gaps in the sequence.  I also thought there were some ETL limitations possibly when using FASTLOAD or MLOAD to populate a table that has an IDENTITY column.

Thanks,

         Mike

Senior Apprentice

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hi Dheem,

when you check the QueryLog after running a CSUM(1,1) you'll notice that a single AMP (usually vproc 0) processed all the data, high cpu/io and spool.

For OLAP functions there's a distribution f the data based on PARTTITION and ORDER and for CSUM(1,1) there's only 1 partition and no order.

Hi Mike,

when you are running out of numbers it's not a limitation of Teradata's IDENTITY, you just used the wrong datatype.

BIGINT should be enough, otherwise there's DEC(38,0).

And the limitations on load utilities have been removed since a few releases.

Dieter

Enthusiast

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Hello Nishant,

Can i just check what did u end up with? Can you also please pass on the code as i am having difficulties getting it to work in TD12.

Cheers

Reddy

Teradata Employee

Re: Best way to generate the Sequential numbers : CSUM or IDENTITY columns?

Reddy, just replied to your post .... please have a look.