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.