My Requirement is to generate the Sequential numbers in Column ..
As It can be done in two ways :
A) define as identity column
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?
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)
Depends on the questions asked by Dieter.
Based on that, you can use:
SUM(1) OVER(ROWS UNBOUNDED PRECEDING)
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?
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)
CSUM(1,1) should never be used, because it skewes to a single AMP
Can you please explain more about this??
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.
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.
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.
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.