Alter table without reorg

I have the following table T1, and I wanted to add a new column C4.

CREATE TABLE T1
(
C1 VARCHAR(50) NOT NULL,
C2 VARCHAR(50),
C3 VARCHAR(50),
CONSTRAINT PK_T1 PRIMARY KEY(C1)
);

alter table T1 add column C4 VARCHAR(50);

It seems reorg is not compulsory in this case. But I worry will this affect the performance of select/insert/update/delete on this table if reorg is not done? Or will there be other potential issues?
TsangAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
On one hand, you've got "only" 10,000,000 rows.  I'd be tempted to just do it.

Another workable solution is to create a new table with the new definition, insert all of the data in the current table into the new table, delete the old table, and rename the new table.  That has the same impact as the ALTER (adding a column) and a REORG, but the table is unavailable for less than a second (between the DELETE and RENAME).


If you really want to just alter the current table, let's do some math.

You've got a table with three fields that are varchar(50), one is not nullable, and want to add a nullable field.

The shortest row would be ('A', NULL, NULL) requiring 5 bytes.

The longest row would be ('A...A', 'B...B', 'C...C') requiring 156 bytes.

Every row also contains a 6 byte header, so every row is between 11 and 161 bytes.  The new table would have rows between 12 and 162 bytes.  Assuming that you're using 4K pages (the default) you've got between 24 and 365 rows per block.  Your new size will have between 24 and 333 rows per block.  (Without knowing your data, it's impossible to know the exact sizes.)

So you've got between 27,400 paqes and 416,000 pages.  That's quite a gap, and every page will have to be read, each row modified, and each page rewritten.  Full pages would have to be split, growing the table/tablespace.

My best guess is that the ALTER would complete in a minute or two.  And the data would wind up somewhat "disjointed".  Depending on the typical data access, that may not be an issue.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Tsang,

No reorg should be necessary as column "C4" is "just data".  A reorg disregards non-key data when restructuring the table.

That said, when the column is added, every column in your table automatically grows by 1 byte to accommodate the "nullable" flag for the new column.  Depending on the data blocking, DB2 may have to move some rows if the total length of all rows in any block exceeds the block size.  This also applies when a row is updated (and the length of the row grows) so your table is subject to that kind of process during normal use.


How many rows are in the table?

Kent
0
 
TsangAuthor Commented:
Hi Kdo,

Thanks your explanation.

So does it mean that, at the time I run the "alter table" command, each row will grow by 1 byte, and those that no longer fit in the blocks will be moved immediately? The reason I have to be so specific is that T1 is a large table, the size is of magnitude 10000000 to 100000000, I worry that it takes long time to complete the whole operation.

On the other hand, "update" is rare, and "delete" is done by batch job once a day at midnight when the traffic is low, so I guess the impact is limited.
0
 
TsangAuthor Commented:
Thanks Kent.

We will change DB structure together when we deploy new project release, so all application will be stopped and there should be sufficient time for a few minutes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.