2

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.

  • 1
    What is the matter? Do you want to perform this task constantly? If so you have big problems with your app's architecture. – neshkeev Aug 26 '14 at 10:30
  • @zaratustra The app had bad architecture, and this is to correct it (it's a very old app) – kev Aug 26 '14 at 10:33
  • I guess you don't have to improve the performance, because your users won't use this feature, will they? – neshkeev Aug 26 '14 at 10:35
  • If this correction has to be done only once, 20 minutes seems ok to me. Why do you need to make it faster? – Frank Schmitt Aug 26 '14 at 10:36
  • You could try putting the data into another table, altering the existing table, and re-inserting it. However, copying the table twice might take as long or longer. – Gordon Linoff Aug 26 '14 at 10:39
5

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.

  • It took about 30 seconds with your solution, nice optimization ! Thanks a lot :) Did not try with NOLOGGING. – kev Aug 26 '14 at 14:20
  • Tell me what solution is faster please – neshkeev Aug 26 '14 at 14:35
  • Parallel DDL + CTAS is the fastest. There was no significant change with parallel DML. – kev Aug 26 '14 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.