Sequence number 2

[MVSTeradata]

I need to create a sequence number on a table. I was using row number logic earlier but it takes forever to update the sequence number as it is huge table and new data is inserted every day. I also tried using the Identity column but the problem is that there is an update through MLOAD and the Identity column is preventing the MLOAD from running.
I would appreciate all the help

 

[dnoeth]

insert into big_tab
select
cnt +
row_number() over (order by xy),
....
from stage_tab,
(select coalesce(max(seq_col), 0) as cnt from big_tab) dt;

should work fast even for large tables.

Instead of calculating the max(seq_col) for each insert, you could keep that value in a table and after the insert just add the activity_count to it.

Btw, identity works with FastLoad/MLoad in V2R5.1

Dieter

 

[EdgarNg]

insert into TableA

select
csum(1,1)
, colA
, colB
from
TableB

--------------------------

that requires no sorting, no ordering, and it works fast!

Edgar

 

[dnoeth]

that requires no sorting, no ordering, and it works fast!
"

And will probably abort with a 2646 "No more spool space", if you try it on a large data set

If you check the spool usage of your query you'll find out that a single AMP has all the spool...
That's why you have to order using column with a good distribution.

Dieter

 

[MVSTeradata]

Thank you all for your help