Many times we need to add new columns (with data) to our data warehouse tables which have a couple of hundred million or even billion rows and a simple ALTER TABLE table_name ADD column_name would take ages.
After refining the process this is the quickest way I could come up with:
0. Generate the DDL script for the existing table including all the indexes with a preferred tool or method. (i.e: select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OWNER') from dual; do the same for the corresponding indexes)
1. Create a new table including the new column based on the existing table:
CREATE /*+ PARALLEL(48) */ TABLE D_SHIPMENT_NEW
AS SELECT S.SK_SHIPMENT_POSITION
,S.SK_SHIPMENT
,S.SHIPPING_NUMBER
,S.SK_ARTICLE
,S.PAY_ID
FROM D_SHIPMENT S
LEFT JOIN PAY D_PM
ON (S.PAY_ID = NVL(D_PM.PM_ID,-1));
2. I usually do a quick comparison just to make sure the tables have the same number of rows:
SELECT COUNT(1) FROM D_SHIPMENT
UNION ALL
SELECT COUNT(1) FROM D_SHIPMENT_NEW;
3. If the numbers match then we are safe to carry on.
DROP TABLE D_SHIPMENT purge;
4. Rename the table
RENAME D_SHIPMENT_NEW TO D_SHIPMENT;
5. Gather stats on the table so Oracle can have some better estimates for the index creation.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'D_SHIPMENT');
END;
/
6. Recreate the indexes, primary key
CREATE UNIQUE INDEX PK_D_SHIPMENT_POS ON D_SHIPMENT(SK_SHIPMENT_POSITION) PARALLEL 48;
ALTER TABLE D_SHIPMENTADD CONSTRAINT PK_D_SHIPMENT_POS PRIMARY KEY (SK_SHIPMENT_POSITION) USING INDEX;
ALTER INDEX PK_D_SHIPMENT_POS NOPARALLEL;
CREATE INDEX IDX_D_SHIP_NR ON D_SHIPMENT(SHIPPING_NUMBER) PARALLEL 48;
ALTER INDEX IDX_D_SHIP_NR NOPARALLEL;
And here you go - you have a new table with the added column including current statistics.
