CODEWITHSUNDEEP
sqloracle-databaseoracle11gsequencealter
ht_3535
ht_3535

Reputation: 373

Add a new column in table with a sequence - Oracle

I have a table that has 60 million rows of data. I would like to introduce a new column say "id" for the table that is an auto incremented sequence.

For example:

CREATE TABLE Persons (
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Persons VALUES ('abc', 'def');
INSERT INTO Persons VALUES ('abcd', 'ghi');

CREATE SEQUENCE "PERSON_SEQUENCE" START WITH 1 INCREMENT BY 1;

ALTER TABLE PERSONS ADD (PERSONID NUMBER);

UPDATE persons SET personid = PERSON_SEQUENCE.NEXTVAL;

In the above sql statements, I am able to create a sequence then alter the table and update it.

Since the amount of data I need to update is large.. I would like to perform this with as much low cost as possible.

I am trying to do so something like this:

ALTER TABLE PERSONS ADD (PERSONID NUMBER DEFAULT(PERSON_SEQUENCE.NEXTVAL));

but the above does not work. Oracle throws me the below error:

Error starting at line :

1 in command - ALTER TABLE PERSONS ADD (PERSONID NUMBER DEFAULT(PERSON_SEQUENCE.NEXTVAL)) Error report -

ORA-00984: column not allowed here 00984. 00000 - "column not allowed here" *Cause:
*Action:

However this works: 

ALTER TABLE PERSONS ADD (PERSONID NUMBER DEFAULT(0));

Could some one help me with how I can achieve to alter a table (create a new column) and populate the column with a seq id both in a single sql. Thank you!

Upvotes: 5

Views: 9785

Answers (2)

Jalute George
Jalute George

Reputation: 39

This worked for me:

alter table PERSONS add (PERSON_ID number default PERSON_SEQ.nextval);

Upvotes: 3

wolφi
wolφi

Reputation: 8361

For a table with 60 million rows, I would not do an add column + insert, but create the table new:

RENAME persons TO persons_old;

CREATE TABLE Persons (
  personid   number,
  LastName   varchar(255),
  FirstName  varchar(255)
); 

INSERT INTO persons (personid, lastname, firstname)
SELECT person_sequence.nextval, lastname, firstname
  FROM persons_old;

DROP TABLE persons_old;

If this is still taking too long, speak to your DBA about ALTER TABLE NOLOGGING and INSERT /*+ APPEND */ and PARALLEL DML.

EDIT: Ah, yes, for 60 million you could even increase the cache size of the sequence for the initial assignment:

ALTER SEQUENCE PERSON_SEQUENCE CACHE 1000;

Upvotes: 7

Related Questions