• 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: ORACLE 12c Release 1

Grouping and counting continuity

15 Tuesday Dec 2015

Posted by troroblog in ORACLE, ORACLE 12c Release 1, SQL, Tipps and Tricks

≈ Leave a comment

Tags

Analytic Functions, Model Clause, ORACLE, ORACLE 12c Release 1, Row Pattern Matching, SQL

A colleague of mine had the problem to group continuous entries of same type to be able to count number of continuos entries.

His Data:

TS        USERNAME RETURNCODE
--------- -------- ----------
05-NOV-15 SCOTT         28000
04-NOV-15 SCOTT         28000
03-NOV-15 SCOTT          1027 
02-NOV-15 SCOTT          1027
01-NOV-15 SCOTT          1027
31-OCT-15 SCOTT          1027
30-OCT-15 SCOTT             0
29-OCT-15 SCOTT          1027
28-OCT-15 SCOTT          1027
27-OCT-15 SCOTT             0
26-OCT-15 SCOTT             0
25-OCT-15 SCOTT          1027

Output needed:

TS        USERNAME RETURNCODE GROUP COUNT
--------  -------- ---------- ----- -----
05-NOV-15 SCOTT         28000     1     2
04-NOV-15 SCOTT         28000     1     2
03-NOV-15 SCOTT          1027     2     4
02-NOV-15 SCOTT          1027     2     4
01-NOV-15 SCOTT          1027     2     4
31-OCT-15 SCOTT          1027     2     4
30-OCT-15 SCOTT             0     3     1
29-OCT-15 SCOTT          1027     4     2
28-OCT-15 SCOTT          1027     4     2
27-OCT-15 SCOTT             0     5     2
26-OCT-15 SCOTT             0     5     2
25-OCT-15 SCOTT          1027     6     1

So basically the problem was to build groups on continuous identical returncodes and increase group number with every change. After those groups have been built the count column can be calculated using a COUNT(*) OVER (PARTITION BY the group-identification).


Solution 1 – ANALYTIC FUNCTION:

This solution compares the returncode value of the previous row (within same username ordered by timestamp) with the current value and returns “1” in case of a difference if the current value is unchanged NULL is returned. In the second step a running summary over this 1/NULL result is built.

WITH data (ts, username, returncode)        
       AS ( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL 
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual) 
SELECT ts
      ,username
      ,returncode
      ,SUM(start_entry) OVER (PARTITION BY username ORDER BY ts DESC) AS grp
  FROM (SELECT ts
             , username
             , returncode
             , CASE
                  WHEN returncode = LAG(returncode) OVER (PARTITION BY username
                                                              ORDER BY ts DESC)
                     THEN 0                   
                  ELSE 1
               END AS start_entry 
          FROM data) 
/

TS        USERN RETURNCODE GRP
--------- ----- ---------- ----------
05-NOV-15 SCOTT      28000          1
04-NOV-15 SCOTT      28000          1
03-NOV-15 SCOTT       1027          2
02-NOV-15 SCOTT       1027          2
01-NOV-15 SCOTT       1027          2
31-OCT-15 SCOTT       1027          2
30-OCT-15 SCOTT          0          3
29-OCT-15 SCOTT       1027          4
28-OCT-15 SCOTT       1027          4
27-OCT-15 SCOTT          0          5
26-OCT-15 SCOTT          0          5
25-OCT-15 SCOTT       1027          6          

Solution 2 – MODEL clause:

The model clauses has the possibility to compare the values of different rows and generate a value for a different column based on this comparision. In our case I compare the value of the returncode of the previous row to the value of the returncode of the current row. In case of identical values i reuse the value of the grp column of the previous row to set the grp value of the current row, in case of a difference I increase the previous rows value by 1.

WITH DATA (ts, username, returncode)
        AS( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp
  FROM data
      MODEL PARTITION BY (username)
            DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY username 
                                                  ORDER BY ts) AS rn)
            MEASURES (1 as grp,returncode,ts)
            RULES AUTOMATIC ORDER
            (grp[rn > 1]  = CASE WHEN returncode[CV()] = returncode[cv()-1] 
                                 THEN grp[CV()-1]
                                 ELSE grp[CV()-1] + 1
                            END)
/

Solution 3 “MATCH_RECOGNIZE (12c)”:

With the MATCH_RECOGNIZE clause we can do a count of the group members simultaneously to the group assignement.

WITH DATA (ts, username, returncode)
        AS(SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
           SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
           SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp, continuous_rows
  FROM DATA
  MATCH_RECOGNIZE (PARTITION BY username
                   ORDER BY ts DESC                    
                   MEASURES FINAL COUNT(*) AS continuous_rows
                           ,MATCH_NUMBER() AS grp
                   ALL ROWS PER MATCH
                   AFTER MATCH SKIP PAST LAST ROW
                   PATTERN (strt cont*)
                   DEFINE cont AS cont.returncode = PREV(cont.returncode)) x
/

ORACLE 12c Release 1 – Session Level Sequences

18 Wednesday Sep 2013

Posted by troroblog in ORACLE 11g Release 2

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, Sequence, SQL

A sequence that starts at the starting point with every session that accesses the sequence…what is this good for?

Now, I could think at things like logging, where we could give every step of a session a ascending step_id, which would be stored in the log table along with the session_id to which the step belonged. Or we could have a transaction which creates a series of entries in the database and within the transaction those entries are numbered for whatever purpose. Generating Primary Keys for a Global Temporary Table would be another field where those kinds of sequences could be used.


Create a session level sequence

A session level sequence is generated using the new SESSION key word. This will restart the sequence for every session.

CREATE SEQUENCE session_seq
   START WITH   1
   INCREMENT BY 1
   SESSION
/ 

Usage

Example filling a global temporary table using a session level sequence as primary key generator:

CREATE SEQUENCE session_seq
 START WITH   1
 INCREMENT BY 1
 SESSION
/

Sequence created.

CREATE GLOBAL TEMPORARY TABLE employees_temp_table (
    employee_id  NUMBER DEFAULT session_seq.nextval NOT NULL
   ,first_name VARCHAR2(20)
   ,last_name VARCHAR2(25)
   )
   ON COMMIT PRESERVE ROWS
/

Table created.

REM ===========================================================================
REM Add rows to table
REM ===========================================================================
INSERT INTO employees_temp_table (first_name, last_name)
SELECT first_name, last_name
  FROM hr.employees
 WHERE rownum < 20
/

19 rows created.

REM ===========================================================================
REM Show the results
REM ===========================================================================
SELECT *
  FROM employees_temp_table
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
          1 Ellen                Abel
          2 Sundar               Ande
          3 Mozhe                Atkinson
          4 David                Austin
          5 Hermann              Baer
          6 Shelli               Baida
          7 Amit                 Banda
          8 Elizabeth            Bates
          9 Sarah                Bell
         10 David                Bernstein
         11 Laura                Bissot
         12 Harrison             Bloom
         13 Alexis               Bull
         14 Anthony              Cabrio
         15 Gerald               Cambrault
         16 Nanette              Cambrault
         17 John                 Chen
         18 Kelly                Chung
         19 Karen                Colmenares

19 rows selected.

If another session does the same thing, the employee_id will start at 1 again.

ORACLE 12c Release 1 – Truncate Cascade

17 Tuesday Sep 2013

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, SQL, Truncate Table

Prior to ORACLE 12c truncating a table referenced by enabled foreign keys was no possible (even if the tables were empty).

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORACLE 12c adds a new option to the TRUNCATE command

  • Parent and all children tables will be truncated if the foreign key between (all of) them is defined as ON DELETE CASCADE
  • Even if no parent exists (optional foreign key)
  • Even if the foreign key is defined to be novalidate
  • It is a truncate not a delete

Example

REM ===========================================================================
REM Create tables
REM ===========================================================================
CREATE TABLE dept_trunc_casc_demo (
   deptno NUMBER
  ,dname  VARCHAR2(30)
  ,CONSTRAINT dept_trunc_cast_demo_pk PRIMARY KEY (deptno)
  )
/


CREATE TABLE emp_trunc_casc_demo (
   empno  NUMBER
  ,ename  VARCHAR2(30)
  ,deptno NUMBER
  ,CONSTRAINT emp_trunc_casc_demo_pk PRIMARY KEY (empno)
  ,CONSTRAINT emp_dept_trunc_casc_fk FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo
  )
/


REM ===========================================================================
REM Add data
REM ===========================================================================
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (10,'ACCOUNTING');
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (20,'SALES');

INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (1,'KUNZ',10);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (2,'MEIER',20);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (3,'HUBER',20);

COMMIT;

REM ===========================================================================
REM Trying to truncate master table
REM Will not work as with prior versions of ORACLE
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo;

TRUNCATE TABLE dept_trunc_casc_demo
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

REM ===========================================================================
REM Trying to truncate master table with new keyword CASCADE
REM This will not work unless there is a FK with on delete cascade.
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo CASCADE;

TRUNCATE TABLE dept_trunc_casc_demo CASCADE;
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "O12TEST"."EMP_TRUNC_CASC_DEMO"

REM ===========================================================================
REM Changing the foreign key to cascade delete
REM ===========================================================================
ALTER TABLE emp_trunc_casc_demo DROP CONSTRAINT emp_dept_trunc_casc_fk;

ALTER TABLE emp_trunc_casc_demo ADD CONSTRAINT emp_dept_trunc_casc_fk
   FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo ON DELETE CASCADE;

REM ===========================================================================
REM Truncating Master Table with cascade keyword
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo cascade;

REM ===========================================================================
REM Show number of rows in child table
REM ===========================================================================

SELECT COUNT(*)
  FROM emp_trunc_casc_demo;

COUNT(*)
--------
       0

ORACLE 12c Release 1 – Native Top-N Queries

03 Tuesday Sep 2013

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, SQL, Top-N

The new row_limiting_clause allows us to perform Top-N queries without having to use analytic functions or nested inline views. We can define rows to be skipped (offset), how to handle ties, percent and absolute number of rows to be returned – just about everything we need.

There is one thing we have to take care by ourselves … the data should be ordered to get a decent result.


Fetching first 10 rows of hr.employees table ordered by salary

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 10 ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Lex                  De Haan                        17000
Neena                Kochhar                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000
Nancy                Greenberg                      12000
Shelley              Higgins                        12000 
Alberto              Errazuriz                      12000
Lisa                 Ozer                           11500

10 rows selected.

Skip first 5 rows and fetch next 2 rows.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Michael              Hartstein                      13000
Nancy                Greenberg                      12000

Handling Ties

When we take a look at the result of the first query, we can see, that besides Nancy Greenberg two more rows exist with exactly the same salary (ties) – to get them by the second query we have to tell ORACLE how to handle ties.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 2 ROWS WITH TIES;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Michael              Hartstein                      13000
Nancy                Greenberg                      12000
Shelley              Higgins                        12000
Alberto              Errazuriz                      12000

Per Cent top-N Queries

A top-N query per cent is also possible. The number of rows to be returned is always a ceiled value (eg. 5% of 107 rows equals 5.35 which will be ceiled to 6 rows).

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 5 PERCENT ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000

6 rows selected.

ORACLE does not care whether you write ROWS or ROW. Furthermore FIRST and NEXT do not have a semantic meaning to the SQL statement and are used for readability issues.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH NEXT 5 PERCENT ROW ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000

6 rows selected.

Limitations

Two things you cannot do:

  • per cent offset
  • offset with ties

Performance

The execution plans for the native top-n queries have fewer steps than the implementations using analytic functions or nested inline views.

Looking at the filters in the execution plans, we will find some “analytic-function-like” implementations.

EXPLAIN PLAN FOR SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 5 PERCENT ROWS ONLY;

Explained.

SELECT *
  FROM TABLE(SYS.DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 720055818
---------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|     Time |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |  107  |  8346 |       3 (0)| 00:00:01 |
|* 1 | VIEW               |             |  107  |  8346 |       3 (0)| 00:00:01 |
|  2 | WINDOW SORT        |             |  107  |  2033 |       3 (0)| 00:00:01 |
|  3 | TABLE ACCESS FULL  | EMPLOYEES   |  107  |  2033 |       3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_
 subquery$_002"."rowlimit_$$_total"*5/100))

16 rows selected.

EXPLAIN PLAN FOR 
SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 PERCENT ROWS WITH TIES;

Explained.

SELECT *
  FROM TABLE(SYS.DBMS_XPLAN.DISPLAY()); 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 720055818
---------------------------------------------------------------------------------
| Id | Operation           | Name        | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |             |  107 |  9737 |       3 (0)| 00:00:01 |
|* 1 | VIEW                |             |  107 |  9737 |       3 (0)| 00:00:01 |
|  2 | WINDOW SORT         |             |  107 |  2033 |       3 (0)| 00:00:01 |
|  3 | TABLE ACCESS FULL   | EMPLOYEES   |  107 |  2033 |       3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber">5 AND
 "from$_subquery$_002"."rowlimit_$$_rank"<=CASE WHEN (5>=0) THEN 5 ELSE
 0 END +CEIL("from$_subquery$_002"."rowlimit_$$_total"*5/100))

17 rows selected. 

Conclusion

In my opinion native top-n query is a very handy feature helping us to write code that is more maintainable and readable. It should be used as soon as we are on ORACLE 12c.

ORACLE 12c Release 1 – Identity Column

09 Tuesday Jul 2013

Posted by troroblog in ORACLE, ORACLE 12c Release 1

≈ Leave a comment

Tags

Identity Column, ORACLE, ORACLE 12c Release 1

For a long time the ORACLE community has waited for something like an auto-increment column on tables. With the latest release of ORACLE this feature was implemented – but what benefits do identity columns have over the good old trigger/sequence solution?

CREATE TABLE employees (employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY
                                           (START WITH 1 INCREMENT BY 1)
                       ,last_name   VARCHAR2(30) NOT NULL)
/

 A identity column is always generated to be NOT NULL.

desc employees

Name                             Null?    Type<
-------------------------------- -------- ------------------------------------
EMPLOYEE_ID                      NOT NULL NUMBER
LAST_NAME                        NOT NULL VARCHAR2(30)

Generate Options

There are 3 different options to generate the identity value

GENERATED BY DEFAULT

  • identity column is populated using the sequence if column is not passed by the insert statement
  • if NULL value is passed you get an error, as NULL is inserted and the column is generated to be NOT NULL

GENERATED BY DEFAULT ON NULL

  • identity column is read from the sequence if columns is not passed or no value is passed by the insert statement
  • same as if you had created a sequence and a default specification

GENERATED ALWAYS

  • if generated always is used as the option you are not allowed to pass a value for the identity column.

Primary Key Constraint

Is an identity column the primary key per default?

Using an identity (surrogate key) column as the primary key of a table makes perfect sense but ORACLE has not implemented an automatism for that, which is perfectly ok. If we want to use the identity column as primary key we have to do it on our own.

CREATE TABLE employees (employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY                                           (START WITH 1 INCREMENT BY 1)
                       ,last_name   VARCHAR2(30) NOT NULL
                       ,CONSTRAINT employees_pk PRIMARY KEY (EMPLOYEE_ID)
                       )
/

Identity Sequence

All identity column statement create a sequence with the name ISEQ$$_[ObjectNumber]. Where ObjectNumber is the number of the object the sequence serves for.

SELECT t.NAME AS TABLE_NAME
      ,c.name AS IDENTITY_COLUMN_NAME
      ,s.NAME AS SEQUENCE_NAME
  FROM            sys.IDNSEQ$ os
       INNER JOIN sys.obj$    t  ON (t.obj# = os.obj#)
       INNER JOIN sys.obj$    s  ON (s.obj# = os.seqobj#)
       INNER JOIN sys.col$    c  ON (    c.obj# = t.obj#
                                     AND c.col# = os.intcol#)
>/ 

TABLE_NAME                     IDENTITY_COLUMN_NAME           SEQUENCE_NAME
------------------------------ ------------------------------ ------------------------------
EMPLOYEES                      EMPLOYEE_ID                    ISEQ$$_93717

Drop Table

Dropping the table having an identity column will not affect the sequence as long as the recycle bin is not purged or the table is not dropped using a drop table … purge command. As soon as the recycle bin is purged the sequence is dropped too. The advantage of this behaviour is, that if a table is undropped using a flashback command the sequence does not need to be recreated as it is still around (and has the correct value).

INSERT INTO employees(last_name) VALUES ('HUBER');

COMMIT;

DROP TABLE employees;

FLASHBACK TABLE employees TO BEFORE DROP; 

INSERT INTO employees(last_name) VALUES ('FLASHBACK');

SELECT * FROM employees;

EMPLOYEE_ID LAST_NAME
----------- ------------------------------
          1 HUBER
          2 FLASHBACK

Rename identity sequence

While renaming the sequence was possible in the first releases of ORACLE 12c this does not longer work – which is a good thing.


Conclusions

Advantages:

  • Now, the declaration effort to be taken is smaller. We do not longer have to write trigger code to populate the surrogate key.
  • A default value may not become invalid – this makes the default value assignment more stable than triggers.
  • With the new default value ON NULL clause the default value will also be taken, when a NULL value is passed.
  • Sequence is dropped when the table it is used for is dropped – so no zombie-sequences will exist in the future. 

Disadvantages:

  • We do not have control over the name of the sequence.
  • I have seen that many surrogate key columns have been sourced using a single sequence. This will not be possible using the “IDENTITY COLUMN” feature.

 

Will I personally make use of the new “identity column” feature?

 Even though I am not really a friend of having objects with artificial names in the application schema I do not see a reason why I should create the sequence and the default assignment by my own.

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