RE: Add column on huge table with default value on huge table
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Arvind.Kumar@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Mar 2009 11:18:25 -0500
Best? hmm. After trying several variations you might settle on a best method
for a given data set and new default value on a specific platform.
A good way to do it is:
1) Add the column nullable.
2) Determine a useful index by which to divide the table into equal
chunks with the number of chunks being the amount of parallelism your cpu
and i/o complex can handle above and beyond whatever other workload you
cannot shut off during this operation. It is useful if the index is well
correlated to the original order of insertion. OR, pick similar fence posts
for betweens based on blockid ranges that evenly divide your table into
disjoint sets of blocks, again to match the useful parallelism.
3) Run the correct number of update scripts in parallel at degree 1
(avoiding the parallel query reassembly overhead) adding the default value
with reasonable sized arrays and commit chunks.
4) alter the column not null
Someone will probably immediately post a better way. You didn't mention
partitioning, so I didn't offer that in the solution.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Arvind Kumar
Sent: Wednesday, March 04, 2009 10:32 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Add column on huge table with default value on huge table
Hello All,
I want to add a column in a table which has more than 50 million rows with
default value and not null constraint, what will be the best way to do it in
minimum time.
Alter table citizen add (citizenstatusid number (2) default 1 not null)
Thanks & Regards,
Arvind Kumar
Disclaimer
This e-mail and any attachments transmitted with it are confidential and
intended solely for the use of the individual or entity to whom it is
addressed. If you have received this e-mail in error please notify the
sender and delete the original message. Any views or opinion expressed in
this e-mail are those of the author and do not necessarily represent those
of the Department of Labour. The recipient should check this e-mail and any
attachments for the presence of viruses and other destructive codes. While
all possible precautions have been taken, the Department accepts no
liability whatsoever for loss or damage resulting from the opening of this
message or attachments, or the use of the information contained in this
message or attachment.
Other related posts:
