Question Re: Schema Update taking a long time in Technology Q&A

Schema Update taking a long time

Former Member — Thu, 08 Jan 2015 16:10:45 GMT

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

Re: Schema Update taking a long time

Breck_Carter — Thu, 08 Jan 2015 19:48:36 GMT

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.

Re: Schema Update taking a long time

VolkerBarth — Fri, 09 Jan 2015 03:57:42 GMT

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.