Insert sequence taking long time

Question

I have a table in Oracle:

table1

record_id    variable     value
1            100          50
1            101          40
2            100          30
2            101          60
1            102          100

It has over 7 million rows. I want to add another column as unique ID. So, the table would look something like:

table1

id          record_id    variable     value
1           1            100          50
2           1            101          40
3           2            100          30
4           2            101          60
5           1            102          100

But when I try to update it using:

UPDATE table1 SET id = my_sequence.nextval;

It is taking a long time. Is there a faster way to get this done?

Here is the create table query:

CREATE TABLE "ABCUSER"."NUM_VAL"
("RECORD_ID" NUMBER(*,0),
"VAR" NUMBER,
"VAR_SEQUENCE" NUMBER,
"VAR_DATATYPE" NUMBER,
"CREATED_DATE" DATE,
"UPDATED_DATE" DATE,
"VAR_VALUE_NUMBER" VARCHAR2(40 BYTE),
"VAR_VALUE_TEXT" VARCHAR2(255 BYTE),
"VAR_VALUE_DATE" DATE,
"ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

CREATE INDEX "ABCUSER"."IMD_TB_UNIQUE_ID" ON "ABCUSER"."NUM_VAL" ("RECORD_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

Sequence:

CREATE SEQUENCE "ABCUSER"."MY_SEQUENCE" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 10186338807 CACHE 100 NOORDER NOCYCLE ;

pOrinG's answer will work, but I do not think that it will yield any great performance benefits, because sequences are highly optimized. If the sequence is the bottleneck, it is very easy to alter the CACHE clause of the sequence from 100 to 1000 or to 10000 to improve its performance by orders of magnitude. I think you should also define what you mean by "a long time", because 7 million records will take some time to update even in the most optimal scenario. — Mike Nakis

krokodilko krokodilko · Accepted Answer · 2017-07-01T10:31:10

Updating all 7 million rows (the whole table) must take some time.
A faster way is to create a copy of this table:

CREATE TABLE NUM_VAL_copy AS SELECT 
   "RECORD_ID"  ,
   "VAR" ,
   "VAR_SEQUENCE" ,
   "VAR_DATATYPE"  ,
   "CREATED_DATE" ,
   "UPDATED_DATE" ,
   "VAR_VALUE_NUMBER",
   "VAR_VALUE_TEXT" ,
   "VAR_VALUE_DATE" ,
   my_sequence.nextval AS "ID"
FROM NUM_VAL;

then index new_table, grant privileges, add constraints on new table etc.
and finally drop old table and rename new table to old name:

DROP TABLE NUM_VAL;
RENAME NUM_VAL_COPY TO NUM_VAL;

Please check the documentation for details: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9019.htm#SQLRF01608

Insert sequence taking long time

3 Answers