Hello,

In the work we have an update script that takes around 20 hours, and some of the most demanding queries are updates where we change some values, something like:

UPDATE table1 SET
    column1 = DECODE(table1.column1,null,null,'no info','no info','default value'),
    column2 = DECODE(table1.column2,null,null,'no info','no info','another default value'),
    column3 = 'default value';

And like this, we have many updates. The problem is that the tables have around 10 millions of rows. We also have some updates where some columns are going to have a default value but they are nullable (I know if they have the not null and a default constrains then the add of such columns is almost immediate because the values are in a catalog), and then the update or add of such columns is costing a lot of time.

My approach is to recreate the table (as TOM said in https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330 ). But I have no idea on how to retrive some columns from the original table, that are going to remain the same and also other that are going to change to a default value (and before the update such column had a sensible info), this because we need to keep some info private.

So, my approach is something like this:

CREATE TABLE table1_tmp PARALLEL NOLOGGING
    AS (select col1,col2,col3,col4 from table1);

ALTER TABLE table1_tmp ADD ( col5 VARCHAR(10) default('some info') NOT NULL;
ALTER TABLE table1_tmp ADD ( col6 VARCHAR(10) default('some info') NOT NULL;

ALTER TABLE table1_tmp ADD ( col7 VARCHAR(10);
ALTER TABLE table1_tmp ADD ( col8 VARCHAR(10);
MERGE INTO table1_tmp tt
    USING table1 t
    ON (t.col1 = tt.col1)
WHEN MATCHED THEN
    UPDATE SET
        tt.col7 = 'some defaul value that may be null',
        tt.col7 = 'some value that may be null';

Hope have an idea on how to improve performance in stuff like this.
Thanks in advance!

CREATE TABLE table1_tmp (
  col1,col2,col3,col4,
  col5 default 'some info' not null, 
  col6 default 'some info' not null,
  col7,col8
)
AS 
  select col1,col2,col3,col4, 
         'some info' col5, 'some info' col6,
         'some defaul value that may be null' col7,
         null col8
  from table1;