• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: DEFAULT

ORACLE 12c Release 1 – New Default behaviour

09 Tuesday Jul 2013

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

DEFAULT, ORACLE, ORACLE 12c Release 1, SQL

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.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...