DEFAULT and NOT NULL become friends in 11g

If somebody would ask me to add a new column to a production table the first thing I would check is how many active transactions on that table are or, in other words, how hot it is from the concurrency point of view. This is because, if there are any active transactions on that table, my attempt to change its structure will simply fail. Of course, an online redefinition could be the right solution but is far more complicated than a simple ALTER TABLE. However, with Oracle 11g things change. My decision of using an ALTER TABLE statement instead of a DBMS_REDEFINITION operation when I am about to add new columns should be driven by the answers to the following questions:
1. Is it appropriate for the new column to be NOT NULL and to have a DEFAULT clause?
OR
2. Is it appropriate for the new column to allow NULLs without having a DEFAULT clause?
OR
3. Is it about a virtual column?
If the answer is YES you can safety go on with the ALTER TABLE ADD COLUMN statement.

Below is the proof:

1. create a dummy table:

create table muci (col1 number, col2 number);

2. from another sqlplus session run the following anonymous block which simulates a mixed workload with: INSERTs, UPDATEs and DELETEs:

-- simulate a workload with a mix of INSERTs, UPDATEs and DELTEs
-- close the sqlplus window in order to terminate
declare
  l_option pls_integer;
begin
  loop
    l_option := round(dbms_random.value(1, 3));
    if l_option = 1 then
       update muci set col2 = dbms_random.value 
          where rowid in (select rowid from muci where rownum <= 1);
    elsif l_option = 2 then
       insert into muci (col1, col2) values (dbms_random.value, dbms_random.value);
    else
       delete from muci where rowid in (select rowid from muci where rownum <= 1);
    end if;
    commit;
  end loop;
end;
/

3. now, run the following statements:

SQL> set timing on
SQL> alter table muci add (col3 number default 0 not null);

Table altered.

Elapsed: 00:00:01.32
SQL> alter table muci add (col4 number);

Table altered.

Elapsed: 00:00:03.36
SQL> alter table muci add (col5 number as (col1+col2) virtual);

Table altered.

Elapsed: 00:00:00.21
SQL> alter table muci add(col6 number default 0);
alter table muci add(col6 number default 0)
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Elapsed: 00:00:00.10

Very nice, except for the last case, when trying to add a column which allows NULLs and also have a DEFAULT clause. Why did not work? The answer relies, for sure, in the way the ALTER TABLE ADD COLUMN was implemented.

If the new column allows NULLs and has no DEFAULT clause then, anyway, the value of the new added column is NULL and being the last one it is not stored therefore no "data reorganization" is required within the table blocks. The rows from the "MUCI" table continue to have the following structure:



However, Oracle knows that there is a third column but its NULL value is not stored within the row.

When a new column is added having the NOT NULL constraint with a DEFAULT clause, the default value for this new column is stored just once as a table metadata. In a way it's the same like in the table compression feature where for repeated values of a column just one single value is stored, being further referenced when the table is queried. In this case, the rows from the MUCI table will still have the same structure:



The nice part is that Oracle knows that there is a third column which, on the row structure level, is like a "last column with NULLs". But how this could be true? The column was defined with a NOT NULL constraint. Well, if the row representation is like a "last column with NULLs" but the column was defined as NOT NULL then Oracle will simply take the default value which is stored in the metadata of the table and that value will be returned.

We can easily test that the rows layout remains the same:

1. create a dummy table:

SQL> create table my_table as select * from all_objects;

Table created.

2. check the corresponding segment size:

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

3. add a new "last column with NULLs":

SQL> alter table my_table add (my_column1 char(2000));

Table altered.

3. check the segment size.

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

Please notice that the size is the same.

4. add a new NOT NULL column with a default value:

SQL> alter table my_table add (my_column2 char(2000) default 'xxx' not null);

Table altered.

4. check the segment size:

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

Please notice that the segment size is unchanged.

So far so good... but what happens when you try do add a column which allows NULLs and also have a DEFAULT clause? In this case Oracle cannot decide if the value of the new added column should be NULL or the default value. For the previous two cases, it was a black&white approach but now, allowing DEFAULTS and NULLS turns out to be a gray problem and it cannot be handled in the same way. I mean, how could ever Oracle figure out whenever it's about a DEFAULT value or a NULL one? There's no possibility therefore the old approach will be used which actually implies re-organizations within the Oracle table blocks that is allocating the extra storage within each oracle block which belongs to the target table in order to accommodate space for the new column with the specified default value.

Let's check:

SQL> alter table my_table add (my_column3 char(10) default 'xxx');

Table altered.

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      9216

Yes, the segment size has increased. Lovely!