question

arpkum2191 avatar image
arpkum2191 asked

how to add a column to table with billion records without bringing down the application running on it

Assume we have no parallel machine for the application to be pointing at, if the development works is going on Primary DB. You can also answer the query considering parallel machine might be there.
columndatawarehouse
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
First of all - regardless of the advice you get, test your scenario on a test box. Restore the production database and test it out, without having to throw up out of nervousity :) Anyway: Add the column as a NULL-able column will have considerably less impact than adding a column which must have an initial value. If you need an initial value, add it first as NULLable, update the existing rows (in batches) and then change to column to NOT NULL. Add a default value for the column already initially, so that new rows get the default value instead of being NULL. That way you can change to NOT NULL later on, when you have updated all rows with a value for the new column.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.