Need Suggestion: Table Updation Slow

[yash]

Hi:
We have Table say 'Purchase'
PO_NUM NUMBER(8) PRIMARY KEY
PO_DESC VARCHAR2(60)

This table has 2Million records.
We have to update Primary Key (PO_NUM),
Currently PO_NUM is of Length 6
We have to make the PO_NUM Length of 8.
Option# 1
So we are planning to prefix '12' with exiting po_num value using BULK BINDING .
It still taking 9-10 Hrs, which is not acceptable.

Option# 2
THEN we tried with DISABLE PRIMARY KEY Constraint and
it took 1Hr 30min to update all the records..
But after update to ENABLE PRIMARY KEY Constraint it took 2Hrs 30Min.
So total time it took 4hrs.

So , Please advise us.. Your suggestion are also most welcome..

Regards

Yash

 

[sem]

I think that updating them in say 20K batches will take less time. I also don't think that you need bulk binding at all:
begin
execute immediate 'alter table Purchase disable primary key';
loop
update Purchase set PO_NUM = PO_NUM + 12000000 where po_num<12000000 and rownum<=20000;
exit when sql%rowcount<20000;
commit;
end loop;
execute immediate 'alter table Purchase enable primary key';
end;

And after all, can you explain why you need to change it???

Regards, Dima

 

[sem]

You may also drop primary key and recreate it with NOVALIDATE option.

Regards, Dima