Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-01-2013
08:04 AM

04-01-2013
08:04 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-02-2013
10:18 AM

04-02-2013
10:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2013
04:43 AM

04-03-2013
04:43 AM

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

....

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2013
07:34 AM

04-03-2013
07:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-04-2013
02:42 AM

04-04-2013
02:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-11-2013
01:44 PM

04-11-2013
01:44 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-12-2013
05:39 AM

04-12-2013
05:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-13-2013
03:11 AM

04-13-2013
03:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-19-2014
03:56 AM

02-19-2014
03:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-19-2014
10:19 AM

02-19-2014
10:19 AM

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

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