Add new id column and fill incremental value to already existing table

Question

I need to add an id column to my table which is having around 500cr rows and update it with incremental value. Please suggest what is the best way of doing it. I tried running a query to update the value and store in new table but the query failed after 6 hrs saying timeout. Please help.

Answer 1

row_number() over() is quite expensive!
You should try row_number() over(partition by some_field) to redistribute numbering across partitions.
Here, you should pick partition field which has reasonable number of distinct values in your table - let's say 1K or so.
After this "first round" - you will have compound key: partition_field + incremental number that you can use as a primary/foreign key or if you want you can then combine further those two into one key