Oracle Schema upgrade with new constraint- how can I reduce upgrade down time ?

Question

I have a oracle repository up and running and has say 10 million records. One of the table is say

CREATE TABLE TABLE_A 
NAME VARCHAR2(128),
VER  VARCHAR2(128),
TYPE VARCHAR2(32),
DESCRIPTION VARCHAR2(256),
CONSTRAINT TABLE_A_PK PRIMARY KEY ("NAME","VERSION");

This table is being used for long and now say I have a requirement to change the primary key constraint. Now I have the requirement to have another column say ID and primary key to be combination of NAME, VER, TYPE and LANG.

In the upgrade script I can have something like

 EXECUTE IMMEDIATE
 ALTER TABLE TABLE_A ADD LANG VARCHAR2(32);
 EXECUTE IMMEDIATE
 UPDATE TABLE TABLE_A SET LANG ='|| 'en_US';
 EXECUTE IMMEDIATE
 UPDATE TABLE TABLE_A SET TYPE='||'n/a'||' WHERE TYPE IS NULL;

Before TYPE can have values and sometimes null. Since after upgrade its part of primary key it cannot be null so making it n/a if its null.

But doing above thing for 10 million records requires upgrade downtime of 5 hours atleast. Is there any other way I can make a previous column as primary key and still won't require much downtime. Kindly also suggest me if I am wrong with my approach. Thanks in Advance

Answer 1

First of all, I don't understand why using EXECUTE IMMEDIATE.

Then, what about creating a PK using Enabled Novalidated Constraints, it will apply to the new inserted rows but not to the old ones. Like that you can run batch to modify existing data to commit the new PK. Find out more : http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i6516

For LANG column, you could also give default value :

ALTER TABLE TABLE_A ADD LANG VARCHAR2(32) default 'en_US';

then

ALTER TABLE TABLE_A MODIFY LANG VARCHAR2(32) default null;

Nicolas.

Answer 2

The current primary key would have a supporting index which is probably a unique index on NAME/VERSION.

Once the columns have been added, you can create a unique index on those four columns. Then replace the primary key constraint, drop the old index (if it doesn't do so automatically when you drop the PK constraint) and use the newly created index.

It won't cut the total time, bu it may allow you to break the whole operation out into, say, 5 1-hour steps, rather than a single 5-hour step.