Friday, 30 September 2016

DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1

DEFAULT Values Using Sequences

In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose.

In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL clause described in the next section.


  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)

INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');


---------- ------------------------------
  1           DESCRIPTION only
  999       ID=999 and DESCRIPTION
               ID=NULL and DESCRIPTION

3 rows selected.


The fact we can use both the NEXTVAL and CURRVAL pseudocolumns gives us the ability to auto-populate master-detail relationships, as shown below.


  id          NUMBER DEFAULT master_seq.NEXTVAL,
  description VARCHAR2(30)

  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)

INSERT INTO master (description) VALUES ('Master 1');
INSERT INTO detail (description) VALUES ('Detail 1');
INSERT INTO detail (description) VALUES ('Detail 2');

INSERT INTO master (description) VALUES ('Master 2');
INSERT INTO detail (description) VALUES ('Detail 3');
INSERT INTO detail (description) VALUES ('Detail 4');

SELECT * FROM master;

---------- ------------------------------
         1   Master 1
         2   Master 2

2 rows selected.


SELECT * FROM detail;

---------- ---------- ------------------------------
         1         1       Detail 1
         2         1       Detail 2
         3         2       Detail 3
         4         2       Detail 4

4 rows selected.


Of course, this would only make sense if you could guarantee the inserts into the detail table would always immediately follow the insert into the master table, which would prevent you from using bulk-bind operations.

A few things to remember about using sequence pseudocolumns as defaults include:

  • During table creation, the sequence must exist and you must have select privilege on it for it to be used as a column default.
  • The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table.
  • If the sequence is dropped after table creation, subsequent inserts will error.
  • Sequences used as default values are always stored in the data dictionary with fully qualified names. Normal name resolution rules are used to determine the sequence owner, including expansion of private and public synonyms.
  • As with any use of a sequence, gaps in the sequence of numbers can occur for a number of reasons. For example, if a sequence number is requested and not used, a statement including a sequence is rolled back, or the databases is turned off and cached sequence values are lost.

DEFAULT Values On Explicit NULLs

In the previous section we saw default values are only used when a column is not referenced in an insert statement. If the column is referenced, even when supplying the value NULL, the default value is not used. Oracle 12c allows you to modify this behaviour using the ON NULL clause in the default definition.

The following example compares the default action of the DEFAULT clause, with that of DEFAULT ON NULL. The example uses sequences to populate two columns, one using the standard DEFAULT clause, the other using the DEFAULT ON NULL clause.

CREATE SEQUENCE default_seq;
CREATE SEQUENCE default_on_null_seq;

  col1        NUMBER DEFAULT default_seq.NEXTVAL,
  col2        NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL,
  description VARCHAR2(30)

INSERT INTO t2 (description) VALUES ('DESCRIPTION only');
INSERT INTO t2 (col1, col2, description) VALUES (999, 999, '999,999,DESCRIPTION');
INSERT INTO t2 (col1, col2, description) VALUES (NULL, NULL, 'NULL,NULL,DESCRIPTION');


---------- ---------- ------------------------------
         1          1     DESCRIPTION only
       999        999 999,999,DESCRIPTION
                     2     NULL,NULL,DESCRIPTION

3 rows selected.


Notice the difference in the way the explicit NULL is handled.

Metadata-Only DEFAULT Values

Prior to Oracle 11g, adding a new column to an existing table required all rows in that table to be modified to add the new column.

Oracle 11g introduced the concept of metadata-only default values. Adding a NOT NULL column with a DEFAULT clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition.

Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a DEFAULT clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as NOT NULL or not. This represents both a space saving and performance improvement.

There are some fairly obvious restrictions on this functionality, but it's worth checking the ALTER TABLE : DEFAULT section of the manual to make sure you are not hitting one.