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

asked 08 Jan '15, 16:10

Brian's gravatar image

Brian
76557
accept rate: 0%

edited 09 Jan '15, 04:12

Reimer%20Pods's gravatar image

Reimer Pods
4.2k344583


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.

permanent link

answered 08 Jan '15, 19:48

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

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.

(09 Jan '15, 03:57) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 08 Jan '15, 16:10

question was seen: 503 times

last updated: 09 Jan '15, 04:12