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

 

[Dagon]

The fastast way is probably to disable the primary key constaint and then do a straight update using something like:

update purchase
set po_num = to_number(to_char(po_num)||'12')

The re-enable the primary key constraint. Updating 2 million rows is never going to be instanteous unless you have an extremely fast machine, so you have to be realistic.

 

[yash]

Thanks Dagon for your Help..