Is that 3-4 billion rows each, or total ?
I would be a little suspicious of an update which populates a new column
with a value derived from existing columns. What options might you have for
declaring a virtual column instead - which you could index if needed.
Be extremely cautious about calculating space requirements - if you're
updating every row on old data might you find that you're causing a
significant fraction of the rows in each block to migrate, and there's a
peculiarity of bulk row migration that can effectively "waste" 25% of the
space in every block that becomes the target of a migrated row.
This effects can be MUCH work when the table is compress (even for OLTP)
since the update has to decompress the row before updating and then only
"re-compresses" intermittently as the block becomes full. The CPU cost can
be horrendous and you still have the problem of migration if the addition
means the original rows can no longer fit in the block.
If it is necessary to add the column you may want to review "alter table
move online" can do in the latest versions (in case you can make it add the
column as you move) or review the options for dbms_redefinition - maybe
running several redefinitions concurrently rather than trying to do any
parallel update to any single table.
Regards
Jonathan Lewis
