update emp_id column with 1 billion records-CodePudding

I have an EMP table with columns

emp_id(number(10)), ename varchar2(25) and DOB (date)

The count of records = 1billion.

The emp_id column is totally null and I have to fill it with unique values.

What are the 3 easy steps to complete the task? Help me with Oracle PL/SQL code to finish this task.

CodePudding user response：

Only 2 steps:

ALTER TABLE emp DROP COLUMN emp_id;
ALTER TABLE emp ADD (emp_id NUMBER GENERATED ALWAYS AS IDENTITY);

db<>fiddle here

Again, 2 steps:

CREATE SEQUENCE emp__emp_id__seq;

UPDATE emp
SET emp_id = emp__emp_id__seq.NEXTVAL;

db<>fiddle here

One step:

If you have overwritten the column data then either ROLLBACK the last transaction or restore the data from backups.

CodePudding user response：

The emp_id column is totally null and I have to fill it with unique values.

If you want to do it one-time-only, then just one step would do:

update emp set emp_id = rownum;

and that column will have unique values. You don't need PL/SQL (but be patient as 1 billion rows is quite a lot, it'll take time).

If you want to automatically populate it in the future, then it depends on database version you use. Before 12c, you'll have to use a sequence and a database trigger. In later versions, you can still use the same (sequence trigger) or - as MT0 showed - identity column.