Adding NOT NULL Columns with DEFAULT values – 11G

In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows.
This would mean that every row in the table was updated with the default value. This could be a pretty nasty side effect, as lengthening every row in the table will inevitably lead to chained rows, and all of the problem that they can cause. It could take a while too… Until Oracle 11g, that is.

From Oracle 11G, if you: ALTER TABLE table ADD ( column col-type DEFAULT def NOT NULL ) the default isn’t actually added to the data. It’s only added to the meta-data. Lets see that in action:

Run utlchain to create CHAINED_ROWS table for analysis

@?/rdbms/admin/utlchain.sql
Table created.

Create table and add constraints

create table ncha.tab1 (pk number, c2 timestamp, filler char(1000)) pctfree 1
alter table ncha.tab1 add constraint tab1_pk primary key (pk)

Table created.
Table altered.

Insert 10,000 rows into the table

insert into ncha.tab1 select rownum id, sysdate, 'A' from dual connect by level <= 10000

10000 rows created.
commit complete.

Gather some stats and have a look after loading the table. Check for chained rows at the same time.

exec dbms_stats.gather_table_stats('NCHA','TAB1',null,100)
select table_name,num_rows,blocks,avg_space,avg_row_len 
  from user_tables 
 where table_name = 'TAB1'

PL/SQL procedure successfully completed.

TABLE_NAME                       NUM_ROWS   BLOCKS  AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- -------- ---------- -----------
TAB1                                10000    1,504          0        1016

analyze table tab1 list chained rows into chained_rows
select count(*) CHAINED_ROWS from chained_rows

Table analyzed.

CHAINED_ROWS
------------
           0

Now lets add the column DEFAULT NOT NULL

alter table ncha.tab1 add (filler_default char(1000) default 'EXPAND' not null)

Table altered.

Gather some stats and have a look after adding the column. Check for chained rows at the same time.

PL/SQL procedure successfully completed.

TABLE_NAME                       NUM_ROWS   BLOCKS  AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- -------- ---------- -----------
TAB1                                10000    1,504          0        2017

Table analyzed.

CHAINED_ROWS
------------
           0

And look at the data:

select filler_default,count(*) from ncha.tab1 group by filler_default

FILLER_DEF   COUNT(*)
---------- ----------
EXPAND          10000

So it’s all there. The metadata is intercepting the nulls and converting them to the default on the fly, rather than storing them in the blocks.
So what happens if we actually UPDATE the table?

NOTE: You get exactly the same results whether updating the column, or just making the column NULL-able. Oracle shuffles off and updates every column for you. The outputs are identical regardless of update or making it NULL-able.

EITHER: alter table ncha.tab1 modify (filler_default null)
Table altered.

OR: update ncha.tab1 set filler_default = 'EXPAND'

10000 rows updated.
Commit complete.

The data looks the same…

select filler_default,count(*) from ncha.tab1 group by filler_default

FILLER_DEF   COUNT(*)
---------- ----------
EXPAND          10000

Gather some stats and have a look after the update, checking for chained rows at the same time.

PL/SQL procedure successfully completed.

TABLE_NAME                       NUM_ROWS   BLOCKS  AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- -------- ---------- -----------
TAB1                                10000    3,394          0        2017

Table analyzed.
CHAINED_ROWS
------------
        5714

So it’s a fair bit bigger – additional blocks have been used to accommodate the row chaining.
So, when adding columns, be aware of side effects, consider that if the column will be subsequently updated, you may be storing row chaining problems for a later date, but avoiding that potentially major issue now.

It’s a good example of the RDBMS performance mantra, which is don’t do now what you may never need to do. Lets avoid unnecessary work!

Finally, beware of a wrong results bug 1106553.1 in 11G, whereby you can get a wrong-results error when joining by a column added with a DEFAULT .. NOT NULL clause. Fixed in 12.1

ALSO, in Oracle 12.1, it’s you can now do this with NULL columns. More of which in another post shortly…