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.
Best Answer
I guess, you can use parallel operations:
Parallel DML
Let your table support parallel operations:
Then you perform all your steps as you describe in the question.
Parallel DDL + CTAS
Then you drop your table:
Then you rename your temp table:
Then you add indexes for to this new table:
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.