var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Set and alter Default value on huge table
We have DB2 9.7 as the database.
Need to add a new column to a huge table (almost 1 billion history records in this fact table). The value on the column should be 1 for 90% of the records.
My approach is adding the column with set default 1. Later alter the column default and set to NULL (in order to handle ongoing process / logic).
Got few questions like
a. Will it affect performance of the DB when set default to 1 and later to NULL
b. Do we need to do RE-ORG on the table, if yes, when?
c. Any other better approach ?
Appreciate your thoughts on this!
a) It will only affect performance when the column is added with default = 1, since it needs to set it to all of the existing rows. Changing it later to null will not be a problem.
b) It depends on what adding the column does to the pages. If a lot of them get overflowed, then yes you should reorg.
c) I really do not see anything better.
Set and alter Default value on huge table
Thanks Andy !
"a) It will only affect performance when the column is added with default = 1, since it needs to set it to all of the existing rows."
Does it take long time to complete this job?
Usually, add column DDL would be quick (though I tried only on small tables) and set it with default value.
actually when you add a column and set a default, it does not get populated on each row of the table. There is a marker put in the catalog. The value does not get put onto the individual rows until you perform a reorg.