I have a table which contains a VARCHAR2 column that must me changed into NUMBER.
The table contains more than 30 millions rows.
The table definition :
CREATE TABLE EFF ( MOD VARCHAR2(6) NOT NULL, MP VARCHAR2(6) NOT NULL, PROG VARCHAR2(1) NOT NULL, RNK VARCHAR2(4) NOT NULL, EFF_STS VARCHAR2(1), EFF_PART VARCHAR2(1), CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, RNK) );
The code I used on Oracle 11g to change the
RNK column type :
ALTER TABLE EFF ADD (NRNK NUMBER(5)); UPDATE EFF SET NRNK = TO_NUMBER(RNK); ALTER TABLE EFF MODIFY (NRNK NUMBER(5) NOT NULL);
Then I would modify the primary key and drop the old column :
ALTER TABLE EFF DROP PRIMARY KEY DROP INDEX; ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK); ALTER TABLE EFF DROP COLUMN RNK;
The UPDATE phase is very long, at least 20 minutes. What could be the best way to do it faster ?
There is no indexes other than the primary key.