Schema Update taking a long time

[Former Member]

We have a table with approxiamately 100 million rows in it.

Here is a shortened table definition:

CREATE TABLE "DBA"."in_invoice_detail" (
"company" NUMERIC(3,0) NOT NULL,
"invoice" NUMERIC(6,0) NOT NULL,
"order_seq" NUMERIC(5,0) NOT NULL,
"date_entered" DATE NOT NULL,
"user_name" VARCHAR(10) NOT NULL,
"brand" NUMERIC(4,0) NOT NULL,
"size" NUMERIC(3,0) NOT NULL,
"invoice_seq" NUMERIC(3,0) NOT NULL,
"qty_ordered" NUMERIC(5,0) NULL,
"qty_sold" NUMERIC(5,0) NULL,
PRIMARY KEY ( "company" ASC, "invoice" ASC, "order_seq" ASC, "date_entered" ASC, "user_name" ASC, "brand" ASC, "size" ASC, "invoice_seq" ASC );

We need to convert our brand, size columns from numeric to integer. I have tested this several times and it takes about 4 hours and expands our database by about 10 GB.

Can anyone give me some insight on why it might take this long and grows our database? Any tips to make this conversion more efficient?

Thanks, Brian

[Breck_Carter]

If there are a lot of 1 or 2 digit NUMERIC values being converted to INTEGER then the space for each of them increases from 3 to 4 bytes. If the pages are packed with rows then many rows will be split which causes time.

An alternative might be to UNLOAD TABLE to a file, DROP TABLE, CREATE TABLE with the new data types, and then LOAD TABLE. The end result would be a well-organized table with no page splits.

The 10 GB might be partly due to the giant checkpoint log; it might be freed up when you restart the server.

[VolkerBarth]

If there are a lot of 1 or 2 digit NUMERIC values being converted to INTEGER...

Just to add/clarify: The possible increase (or decrease) in required storage will depend on the actual values of the NUMERIC field (or on formerly stored values of the same row), not on the declared precision/scale.