Tags

, , ,

Up to ORACLE 11g DEFAULT values on columns had no effect when a NULL value was passed for the column. Furthermore it was not possible to use a sequence nextval as the default value for a column.

This has changed with ORACLE 12c. A default declaration may now have an instruction towards NULL handling.

CREATE TABLE employees (employee_id    NUMBER NOT                          NULL
                       ,last_name      VARCHAR2(30)                    NOT NULL
                       ,department_id1 NUMBER       DEFAULT ON NULL 50 NOT NULL
                       ,department_id2 NUMBER       DEFAULT 50)
/

When the default value was assigned like shown above for the column department_id1 it will also have an effect when NULL is passed as a value for the column having a default value:

INSERT INTO employees VALUES (1,'TROLLER',NULL,NULL);
INSERT INTO employees (employee_id, last_name) VALUES (2,'HUBER');
INSERT INTO employees VALUES (3,'MEIER',DEFAULT,DEFAULT);

SELECT * FROM employees;

EMPLOYEE_ID LAST_NAME                      DEPARTMENT_ID1 DEPARTMENT_ID2
----------- ------------------------------ -------------- --------------
          1 TROLLER                                    50 
          2 HUBER                                      50             50 
          3 MEIER                                      50             50

Sequence as a default value

It is also possible to assign a sequence to a default value.This is also done when defining a column as identity column. The advantage of creating your own sequence and using it in a default assignment is the possibility to choose a decent name for the sequence and to be able to use one sequence to populate the surrogate of many tables (if this is something you want to do).

CREATE SEQUENCE employees_seq;

CREATE TABLE employees (employee_id NUMBER       DEFAULT ON NULL employees_seq.nextval NOT NULL
                       ,last_name   VARCHAR2(30)                                       NOT NULL);

INSERT INTO employees VALUES (NULL,'MEIER');
INSERT INTO employees VALUES (DEFAULT,'TROLLER');
INSERT INTO employees(last_name) VALUES ('HUBER');

SELECT * FROM employees;

EMPLOYEE_ID  LAST_NAME 
----------- ------------------------------
          1 MEIER 
          2 TROLLER
          3 HUBER

Performance

Performance comparison using a sequence where the value is assigned to the column using DEFAULT keyword and the “old-fashioned” trigger-solution.

=================================================
COMPARISION DONE POPULATING 100000 ROWS
=================================================
TRIGGER SOLUTION : Elapsed= 519 hsecs|CPU= 518 hsecs
DEFAULT SEQUENCE : Elapsed= 73 hsecs|CPU= 72 hsecs

Thumbs up In my opinion the changes to the DEFAULT behaviour is a big progress and I am pretty sure, that this will influence the usage of this feature.