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
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.