1 2 Previous Next 23 Replies Latest reply on Jul 29, 2010 6:50 PM by Arup Nanda_2 Go to original post
      • 15. Re: Adding column with default value
        SomeoneElse
        Have a look at the add_column_clause section of ALTER TABLE:

        http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_3001.htm#i2198241

        It describes the new behavior and the restrictions:
        • 16. Re: Adding column with default value
          EdStevens
          SomeoneElse wrote:
          Have a look at the add_column_clause section of ALTER TABLE:

          http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_3001.htm#i2198241

          It describes the new behavior and the restrictions:
          I stand corrected. I wasn't aware of the change for v11.
          • 17. Re: Adding column with default value
            SomeoneElse
            I stand corrected. I wasn't aware of the change for v11.
            Me neither.

            Maybe I should do a diff on each manual.
            • 18. Re: Adding column with default value
              jgarry
              Yes the 11.1 and 11.2 manuals are slightly different.

              11.1:
              When you add a column, the initial value of each row for the new column is null. If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.
              
              This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any AFTER UPDATE triggers defined on the table. However, the optimized behavior is subject to the following restrictions:
              11.2:
              When you add a column, the initial value of each row for the new column is null.
              
                    If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.
              
                    This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any update triggers defined on the table. In this release, no triggers are fired because the default is stored only as metadata. The optimized behavior is subject to the following restrictions:
                        
                          The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
                        
                          The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column.
                 
                    If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.
              So perhaps there is a doc bug, as to whether "earlier releases" includes 11.1? Or is it that extra blurb about "no triggers are fired" was left out of 11.1 doc and it didn't work in 11.1 anyways? We can't really tell from Arup Nanda's undated article which version he tried it on, if any. (No offense to Arup intended, slap would be at Oracle for not including a date on the article. Unless he really didn't try it, which I wouldn't presume, it could be that it worked on a pre-release or something.)
              1 person found this helpful
              • 19. Re: Adding column with default value
                mohitanchlia
                I've opened a case with Oracle so we'll find out. But for sure it doesn't work in 11.1 when I tested.
                • 20. Re: Adding column with default value
                  Arup Nanda_2
                  I'm the author of the article you have referenced. I'm not sure I understand how the article conveys the impression of running the command in sub-seconds. The comamnd does not generate any additional redo and undo when you use default clause on a not null column. since undo and redo are not generated, it is faster; but not sub-seconds as the dictionary needs to be updated.

                  Here is a test (on 11.1.0.6). On my database "redo size" statistic has the number 140; so that's what I check first.

                  SQL> select * from v$mystat where statistic# = 140;

                  SID STATISTIC# VALUE
                  ---------- ---------- ----------
                  122 140 5253248

                  1 row selected.

                  Elapsed: 00:00:00.00

                  -- Let's alter the table

                  SQL> alter table arup.large_table1 add col5 varchar2(20) default 'XXXXXXXX' not null;

                  Table altered.

                  Elapsed: 00:00:03.33
                  SQL> @redo

                  SID STATISTIC# VALUE
                  ---------- ---------- ----------
                  122 140 5301084

                  1 row selected.

                  Elapsed: 00:00:00.02

                  -- Redo generation was 5301084-5253248 = 47836

                  -- Now let's add a nullable column with default value

                  SQL> alter table arup.large_table1 add col6 varchar2(20) default 'XXXXXXXX';

                  Table altered.

                  Elapsed: 00:00:04.49


                  SQL> @redo

                  SID STATISTIC# VALUE
                  ---------- ---------- ----------
                  122 140 8014116

                  1 row selected.

                  Elapsed: 00:00:00.00

                  -- Redo generated was

                  8014116-5301084=2713032

                  Redo is 2.7 million as opposed to 47K in the first case. So, the behavior is as expected and as explained in the article.

                  Hope this helps.

                  Arup
                  • 21. Re: Adding column with default value
                    Arup Nanda_2
                    Even adding NOT NULL is not helping. It's just trying to update all the rows.
                    How do you know that? Could you post the values of redo size stat before and after the alter? To comapre, please post the stats of the alter without the NOT NULL as well.
                    • 22. Re: Adding column with default value
                      jgarry
                      I think there is confusion between not generating redo, which point you are making in your article, and updating all table columns here. The second link in the original post mentions the sub-seconds.

                      See SomeoneElse's demonstration of the subsecond alter to millions of rows, above. As the manual says
                      If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.
                      So obviously, not much redo there.
                      • 23. Re: Adding column with default value
                        Arup Nanda_2
                        The second link in the original post mentions the sub-seconds
                        @jgarry - thank you.

                        Original Poster - is this the case? If your "sub-second" attribution was based on the second link on your post, I would imagine you should have addressed that to the author of that link, rather than referencing my article, no?
                        1 2 Previous Next