Fastest way to change column type from varchar2 to number on large table in Oracle

Question

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.

Answer 1

I guess, you can use parallel operations:

Parallel DML

Let your table support parallel operations:

ALTER TABLE EFF parallel;

Then you perform all your steps as you describe in the question.

Parallel DDL + CTAS

CREATE TABLE temp_table_EFF
PARALLEL
AS
SELECT MOD, MP, PROG, TO_NUMBER(RNK), EFF_PART
  FROM EFF 

Then you drop your table:

DROP TABLE EFF;

Then you rename your temp table:

ALTER TABLE temp_table_EFF RENAME TO EFF;

Then you add indexes for to this new table:

ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);

I have no tables with 30M records, so I can't tell you what method is faster. Try both. If you think that there won't be decent performance improvement try to use the NOLOGGING option to forbid your table generate the redo information.