int not 100% continuous

[mche...@gmail.com]

hi

   i have column which is bigint and not int not 100% continuous , so the data is [1,2,36,10,11] , now i want to reinput the data ane make it continuouse, such as [1,2,3,4,5,6]. I got 20 millions rows, what is the fastest way to do this?

thanks

Peter

[Evgenij Ryazanov]

Hi!

If you don't care about exact ordering, the fastest way is

UPDATE tableName SET columnName = ROWNUM();

If you want to preserve it, a slower command is needed:

MERGE INTO tableName USING

(SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) T(columnName, R)

ON tableName.columnName = T.columnName
WHEN MATCHED THEN UPDATE SET columnName = T.R;

This command also requires a lot of memory (because H2 cannot buffer window functions on disk).

If you have an ascending index on tableName(columnName), you can try to use more efficient version without window functions:

MERGE INTO tableName USING

(SELECT columnName, ROWNUM() FROM tableName ORDER BY columnName) T(columnName, R)

ON tableName.columnName = T.columnName

WHEN MATCHED THEN UPDATE SET columnName = T.R;

Without a compatible index ROWNUM() can number rows in order different from specified in ORDER BY clause.

[mche...@gmail.com]

thank you very much :-)