how to generate incremental sequence numbers in complicated scenarios

Database
Fan

how to generate incremental sequence numbers in complicated scenarios

Hi,

TABLE A

Data: 650 Million

columns: Col1,col2,col3,col4..... to Col40

Primary index(col1,col2,col3,col4,col5)

 

Table B

Data: 300Million

Columns: Col1,col2,col3,col4  to Col41

Primary Index: (Col41)

 

Scenario1 : I would like to add sequence numbers to the teradata table 'A' without altering 650M data. 

 

Scenario2: have to insert the 300M data from table 'B' into Table ''A''. Please note there is no unique combination of data in Table B for columns from 1 to 40.

(data in table 'B' is not unique with the same composite keys as in table A)

 

 

so the final table should have 650 +300=950 millions of data with sequnece numbers to it.

Can someone suggest me, what is the best way to create the sequnece numbers in such scenarios? 

 

Possible solutions tried and not working

1) used identity( does not gives uinity)

2) CSUM ( no good distribution on AMPS)

3) row_number() should always have  order by clause or partition by clause and I dont have unique attribute in table 

4) Sequnce generator in Informatica, but PDO is not working due which it is very slow.

 

any ledas will be appreciated.

Thank you

 

 

4 REPLIES 4
Highlighted
Ambassador

Re: how to generate incremental sequence numbers in complicated scenarios



Can someone suggest me, what is the best way to create the sequnece numbers in such scenarios? 

Define sequence number:

- sequential number starting with n without gaps?

- a unique number with possible large gaps?

 


Possible solutions tried and not working

1) used identity( does not gives uinity)

2) CSUM ( no good distribution on AMPS)

3) row_number() should always have  order by clause or partition by clause and I dont have unique attribute in table 

4) Sequnce generator in Informatica, but PDO is not working due which it is very slow.


#1: is unique if you don't use CYCLE, but you might need a BIGINT

#2: shorthand for ROW_NUMBER, really bad dummy ordering, i.e. CSUM(1,1)

#3: no need for a PK, simply try order by PI columns

#4: :-)

 

Anyway, without altering 650M data the only way is a new table and the easiest way are two MERGEs into a new table with identity.

Fan

Re: how to generate incremental sequence numbers in complicated scenarios

Thank you Dnoeth for your answer

Fan

Re: how to generate incremental sequence numbers in complicated scenarios

Hi Dnoeth,

 

I need sequence numbers with no gaps and starting with 1,2,3,4 till 630 million 

Teradata Employee

Re: how to generate incremental sequence numbers in complicated scenarios

So do the first MERGE with option 3:  ROW_NUMBER() OVER (ORDER BY <PI columns>)

and for the second MERGE use (SELECT MAX(sequence_number) FROM target_table)+ROW_NUMBER() OVER (ORDER BY <PI columns>) to continue the sequence.