12 Replies Latest reply: Apr 25, 2012 12:06 AM by 692223 RSS

    Update on null values in existing column vs add column not null default 11g


      Let us take a following scenario 1.*

      We have a large partitioned table LARGE_TABLE1 (let's say: NUM_ROWS     5 904 977 029, Size     326 GB ) in Oracle 11 database.

      We want to add a new column NEW_COLUMN (NUMBER) and give each existing record in that table a value -999 in NEW_COLUMN.
      In Oracle 11 it would probably take a second since the default value -999 would be permanently added in metadata without updating each and every of 6 bln records.

      Let us take a following scenario 2.*

      We have another large partitioned table LARGE_TABLE2 (of the similiar size as LARGE_TABLE1) in Oracle 11 database.

      We have an existing column EXISTING_COLUMN (NUMBER) that contains 90% null values (90% of partitions contain only null values in the field and 10% of partitions contain only non-null values in that field)

      I would like to replace null values in EXISTING_COLUMN with number -999.

      Updating the entire column would not ever end :-) and doing so in a loop (partition by partition) would take a few days.

      Do you know a way to somehow write that -999 "globally" in metadata like in Scenario 1 instead of updating null value in every record to -999? Any clever workarounds would be appreciated...

      Edited by: SwPiotr on 2012-04-24 07:26