1 2 Previous Next 23 Replies Latest reply on Jul 29, 2010 6:50 PM by Arup Nanda_2

    Adding column with default value

    mohitanchlia
      Oracle 11.0.1.7:

      I am assing a column with detault value of -1 as below. This table has 60M rows. But according to Oracle docs it's supposed to be fast and shouldn't cause any outage. However what I am seeing is that when I run this ddl it just takes forever to add the column and is also causing timeouts when application is trying to insert rows. Could someone please help me and see if I am doing something wrong or if there is any catch.

      http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html

      http://www.oracle.com/technology/deploy/availability/htdocs/online_ops.htm

      These links suggest it should finish in sub seconds and not cause any outage.
      ALTER TABLE C_MESSAGE ADD CN Number(19,0) DEFAULT -1; 
      Edited by: user628400 on Jul 28, 2010 10:49 AM
        • 1. Re: Adding column with default value
          SomeoneElse
          But according to Oracle docs it's supposed to be fast
          Simply adding a column is fast. But if you want to populate it, it has to update every row in the table.
          • 2. Re: Adding column with default value
            mohitanchlia
            But I am only running
            ALTER TABLE C_MESSAGE ADD CN Number(19,0) DEFAULT -1;
            This link http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html has similar example and suggests it should be within subseconds
            alter table sales add tax_code varchar2(20) default 'XX' not null;
            am I missing something?
            • 3. Re: Adding column with default value
              617785
              user628400 wrote:
              But I am only running
              ALTER TABLE C_MESSAGE ADD CN Number(19,0) DEFAULT -1;
              This link http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html has similar example and suggests it should be within subseconds
              alter table sales add tax_code varchar2(20) default 'XX' not null;
              am I missing something?
              The fact that you give the column a default value means it has to touch every row in the table to physically add the column with its default value.
              • 4. Re: Adding column with default value
                SomeoneElse
                am I missing something?
                Or maybe I am.

                I read the article and didn't see anything about adding a column with a default value running quickly.

                Can you point out exactly where it says that?
                • 5. Re: Adding column with default value
                  Kevin2010
                  As per the documentation, looks like it adds the default info to Dictionary, Can you try adding NOT NULL constraint and see the performance.

                  ALTER TABLE C_MESSAGE ADD CN Number(19,0) DEFAULT -1 NOT NULL;
                  • 6. Re: Adding column with default value
                    729338
                    Hi,

                    You are correct. Adding a column with default value on a empty table will take few seconds but with 50 million rows it will take time as it has to populate each row with default value. Other option will be

                    1. Add the column without any default value (should be quick)
                    2. Update the column with the value ( Will take some time)
                    3. Then alter the column to have default value (Should be quick, as nothing to update)

                    The other procedure is also doing the same thing, but doing it in one shot.

                    Regards
                    1 person found this helpful
                    • 7. Re: Adding column with default value
                      mohitanchlia
                      Even adding NOT NULL is not helping. It's just trying to update all the rows.
                      • 8. Re: Adding column with default value
                        mohitanchlia
                        This is from the link that I posted earlier:
                        Adding Columns with a Default Value
                        
                        Although happy with this feature alone, Jill ponders another issue somewhat related to the first one. She wants to add the column TAX_CODE but it has to be NOT NULL. Obviously when she adds a not null column to a non-empty table, she has to also specify a default value, 'XX'. So she writes the following SQL: 
                        alter table sales add tax_code varchar2(20) default 'XX' not null;
                        
                        But she stops there. The table SALES is huge, about 400 million rows. She knows that when she issues the statement, Oracle will add the column alright but will update the value 'XX' in all rows before returning control back to her. Updating 400 million rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead. So Jill has to ask for a "quiet period"—an outage—to make this change. But is there a better approach in Oracle Database 11g? 
                        There is. The above statement will not issue an update to all the records of the table. Well, that's not a problem for new records where the value of the column will be automatically set to 'XX', but when the user selects this column for an existing record, that will return NULL, right?
                        
                        Wrong, actually. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user. So, you kill two birds with one stone: you can define a new column as not null and with a default value and still not incur any penalty for redo and undo generation. Nice.
                        • 9. Re: Adding column with default value
                          Kevin2010
                          Be Careful adding the column with DEFAULT value with NOT NULL constraint on 11g as specified in the Article.

                          http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html

                          Metalink Note :1106553.1 states a bug which can be fixed by applying Patch 9170308 on 11g and it is fixed in server patch 12.1

                          Bug 9170308 WRONG RESULT OCCURS AFTER ALTER TABLE ADD COLUMN

                          The concerned column was added with a DEFAULT value and NOT NULL constraint as follows:
                          ALTER TABLE <table name> ADD <column name> NUMBER(4) DEFAULT <default value> NOT NULL;

                          The bug could also be triggered by any of the following conditions:
                          1. Wrong result
                          2. The column was added with a DEFAULT value and NOT NULL constraint
                          3. The added column is in select list and it is referenced by outer query
                          4. The table to which column was added is involved in outer join



                          Solution
                          The relevant bug will be fixed in 12.1. To fix the problem in 11g, please do the following:
                          1. Apply Patch 9170308.
                          2. Then recreate the affected table(s).

                          Kindly note that patch will fix the problem but table(s) with columns added before applying the noted patch, that meet the above conditions, need(s) to be recreated.

                          The possible workarounds for the issue are as follows:
                          1. Use NVL2 function for the added column in the query.
                          2. Re-create the table instead of adding the column.
                          3. Set "_add_col_optim_enabled"=false before adding a column.
                          • 10. Re: Adding column with default value
                            mohitanchlia
                            Thanks. But my initial DDL doesn't include NOT NULL so I think we are ok.

                            But still my question remains, like I posted the snippet of description from the article, which states that DDL with default value should finish real fast since it just adds an entry in the dictionary. So I don't understand what am I doing wrong.
                            • 11. Re: Adding column with default value
                              617785
                              user628400 wrote:
                              Thanks. But my initial DDL doesn't include NOT NULL so I think we are ok.

                              But still my question remains, like I posted the snippet of description from the article, which states that DDL with default value should finish real fast since it just adds an entry in the dictionary. So I don't understand what am I doing wrong.
                              What you are doing wrong is ignoring the fact that adding the "default value" is causing the alter statement to have to physically update every one of your 60 million rows on the table ... as has already been pointed out multiple times. Every block has to be read from disk into a db buffer, the rows on the block updated and eventually written back to disk. And since you are physically adding length to each row, there will be a lot of row migration and resultant block reorganization, which will cascade to updating all the indexes on the table to change the pointers to the rows, and those index updates in themselves are block reads,update,writes, plus all the attendant rollback and redo info to be written..... It's not just a single update to the data dictionary.
                              • 12. Re: Adding column with default value
                                SomeoneElse
                                So I don't understand what am I doing wrong.
                                The difference is the NOT NULL constraint:
                                SQL> select * from v$version;
                                
                                BANNER
                                --------------------------------------------------------------------------------
                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                                
                                SQL> select count(*) from C_MESSAGE;
                                
                                            COUNT(*)
                                --------------------
                                             2775762
                                
                                SQL> set timing on
                                SQL> ALTER TABLE C_MESSAGE ADD CN Number(19,0) DEFAULT -1;
                                
                                Table altered.
                                
                                Elapsed: 00:02:06.08
                                
                                SQL> ALTER TABLE C_MESSAGE ADD CN2 Number(19,0) DEFAULT -1 not null;
                                
                                Table altered.
                                
                                Elapsed: 00:00:00.21
                                1 person found this helpful
                                • 13. Re: Adding column with default value
                                  mohitanchlia
                                  I am seeing same behaviour and I understand it is trying to update every row. But my question is that then why Oracle has published this example (from my links above):
                                  Although happy with this feature alone, Jill ponders another issue somewhat related to the first one. She wants to add the column TAX_CODE but it has to be NOT NULL. Obviously when she adds a not null column to a non-empty table, she has to also specify a default value, 'XX'. So she writes the following SQL: 
                                  alter table sales add tax_code varchar2(20) default 'XX' not null;
                                  
                                  But she stops there. The table SALES is huge, about 400 million rows. She knows that when she issues the statement, Oracle will add the column alright but will update the value 'XX' in all rows before returning control back to her. Updating 400 million rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead. So Jill has to ask for a "quiet period"—an outage—to make this change. But is there a better approach in Oracle Database 11g? 
                                  There is. The above statement will not issue an update to all the records of the table. Well, that's not a problem for new records where the value of the column will be automatically set to 'XX', but when the user selects this column for an existing record, that will return NULL, right?
                                  
                                  Wrong, actually. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user. So, you kill two birds with one stone: you can define a new column as not null and with a default value and still not incur any penalty for redo and undo generation. Nice.
                                  • 14. Re: Adding column with default value
                                    mohitanchlia
                                    Thanks! even with NOT NULL it is not returning and it looks like it is updating the rows. I have opened a case with Oracle. It could be a problem with 11.0.1.7.

                                    So this is what I am planning to do:
                                    ALTER TABLE C_MESSAGE ADD CN Number(19,0);
                                    ALTER TABLE C_MESSAGE MODIFY CN Number(19,0) DEFAULT -1;
                                    the above statement finishes in secs. Is there any downside of it? We only care about values being -1 for newly inserted rows.

                                    Edited by: user628400 on Jul 28, 2010 12:20 PM
                                    1 2 Previous Next