Oracle 12C Default Column Value Enhancement

I remember that a number of times, developers have approached me with the question – How do I auto increment a default value in a table column? These were mostly folks who had worked in the MS SQL, Mysql and other database environments where this features exists. Now, Oracle 12c Default Column Value feature has been incorporated and puts this to rest.

Read more on other Oracle 12c topics
• Database Identity Columns
• Default Column Value Enhancement

Oracle 12c Default Column Value

So lets look a little deeper at this, focusing on the following.

• Default Values using Sequences
• Default Values on explicit Nulls
• Metadata Only Default Values

Default Values using Sequences

Now, you can use the NextVal and CurrVal sequence pseudo-columns as a default value for the table columns. This feature is pretty much similar to the Identity Columns feature, which has also been introduced in 12c. The only difference between the two is that in using the Sequence as a default value, you cannot have an implicit “Not Null” and “Not Deferrable” constraints.

In this example here we will create a table which will use a sequence as a default value. The sequence should already exist in the database before the table creation.

SQL> create sequence t1_seq;
Sequence created.

SQL> create table t1 (
2 id number default t1_seq.nextval,
3 name varchar2(20)
4 );
Table created.

SQL> insert into t1(id,name) values(1,'John');
1 row created.

SQL> insert into t1(name) values('Mike');
1 row created.

SQL> insert into t1(id,name) values(NULL,'Jack');
1 row created.

SQL> select * from t1;
ID NAME
---------- --------------------
1 John
1 Mike
Jack

We can also use the CurrVal pseudocolumn of the sequence to populate values. This can be very helpful if we need to maintain a parent child relationship. Below are some of the other conditions which apply when using the sequence as a default value for the column.

• Sequence must already exist and the user creating the table or inserting rows must have select privileges.
• If sequence is missing or dropped after table creation, subsequent sequence creation will result in error.
• If the sequence is dropped after the table creation the insert will result in error.
• Sequence used is stored in the data dictionary and the normal naming conventions apply.
• Use of sequence is subject to the same conditions as a normal sequence,including gaps due to misused sequence values.

Default Values on Explicit Nulls

In the example above we have see the default values being set by a sequence when the column is not referenced in the insert statement. If column is referenced in an insert statement then the value is taken from that insert even if the value is NULL. Oracle 12c database allows you to change this behavior using the “On Null” clause while specifying the default values.

Let’s create another column in our sample table above, using this “On Null” clause and test it.

SQL> truncate table t1;
Table truncated.

SQL> create sequence t1_seq_on_null;
Sequence created.

SQL> alter table t1 add id2 number default on null t1_seq_on_null.nextval;
Table altered.

SQL> insert into t1(id,name,id2) values(1,'John',101);
1 row created.

SQL> insert into t1(name) values('Mike');
1 row created.

SQL> insert into t1(id,name,id2) values(null,'John',null);
1 row created.

SQL> select * from t1;
ID NAME ID2
---------- -------------------- ----------
1 John 101
21 Mike 1
John 2

The notable difference here is the third row. Here we provided a Null value for both the columns. This time however the column ID2, which has the “On Null” clause defined, placed a sequence value in the table for this column where the insert statement passed a Null value.

Metadata Only Default Values

This feature was first introduced in 11g to address a very serious issue. Suppose you have a table which has records in the millions and you need to add another “Not Null” column to this table. The task was tedious because it required  that you provide a default value for new column as it is a mandatory column of table. When you provided a default value, Oracle had literally update all the exiting millions of records with the default value for this new column. This is some serious work and would require a huge amount of application down time to complete this.

Oracle 11g simplified this process by introducing the “Metadata only” default value feature. Using this method when you added a new mandatory column for the table with a default value, the value was stored in the Metadata only. The Optimizer rewrites the query at run time to take that value from the Metadata every time it was accessed. This was a huge space and time saving.

Oracle 12c database takes this one step further by introducing this concept for both mandatory and optional columns. Now “Metadata Only” default values are available regardless of the fact that column is “Not Null” column or it is an optional one.