• 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: SQL

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.

Date arithmetic

03 Tuesday Sep 2013

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

DATE, ORACLE, SQL

The hard way and the smart way

A problem that often arises in the classroom is how to calculate the difference (hours, minutes and seconds) of two DATE values.


The hard way

At various places on the web you may find solutions like this:

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC((date_to - date_from) * 24),'FM00') || ':' 
    || TO_CHAR(TRUNC(((date_to - date_from) * 24 - TRUNC((date_to - date_from) * 24)) * 60),'FM00') || ':' 
    || TO_CHAR(((date_to - date_from) * 24 * 60 - TRUNC((date_to - date_from) * 24 * 60)) * 60,'FM00') AS time
  FROM example_data;

TIME
-----------
03:46:51

Personally I would call this the hard way to solve the problem, because there is a much easier solution for this problem.


The smart way

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS') as TIME
  FROM example_data;

TIME
--------
03:46:51

This solution makes use of the date arithmetic logic of ORACLE where the difference between two dates values is expressed as a number representing the number of days including a fraction of days. Furthermore adding a number to a date again results in a date data type. So if we add the difference of two date values to midnight and use TO_CHAR to display the time component of the result, this result will express the difference of those two dates. Now what should we do, if the difference is more than one day?
We just could make use of the day in year format after having added the difference between the two date values to the 31st of december.

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL
                      UNION ALL
                      SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('03.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT CASE
          WHEN TRUNC(date_to - date_from) > 0
             THEN TO_CHAR((TRUNC(SYSDATE,'year')-1) + (date_to - date_from),'DDD HH24:MI:SS')
          ELSE TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS')
       END as TIME
  FROM example_data;

TIME
------------
03:46:51
002 03:46:51

This solution represents in my opinion the smart way to calculate time difference between two date values.

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

Column to rows

23 Thursday May 2013

Posted by troroblog in ORACLE, Regular Expression, SQL, Tipps and Tricks

≈ Leave a comment

Tags

CONNECT BY, ORACLE, Regular Expression, SQL

How can we convert a string including seperators into it’s token using only SQL?

Easiest way is to make use of recursive query techniques and regular expression as shown below:

WITH data (string) AS (SELECT 'MEIER,HUBER,MUELLER,KUHN,,KOLLER' FROM dual)
SELECT REGEXP_SUBSTR (data.string, '[^,]+', 1, ROWNUM) element_string  
  FROM data 
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (DATA.STRING, '[^,]+'))  + 1
/

ELEMENT_STRING
--------------
MEIER
HUBER
MUELLER
KUHN
KOLLER
(null)

Recursive WITH Clause (4)

23 Thursday May 2013

Posted by troroblog in ORACLE 11g Release 2

≈ Leave a comment

Tags

CONNECT BY, ORACLE, RECURSION, SQL

How to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.


Leaf Nodes

Using CONNECT BY the pseudo column CONNECT_BY_ISLEAF can be used to determine whether a node is a leaf node (has no children) or an inner node. The value of the pseudo column CONNECT_BY_ISLEAF is “0” for an inner node and “1” for leaf nodes.

SELECT LEVEL, ename, CONNECT_BY_ISLEAF leaf, empno, mgr
  FROM emp e
 WHERE deptno = 10
CONNECT BY PRIOR empno = mgr
 START WITH mgr is NULL
 ORDER SIBLINGS BY ename
/

LEVEL      ENAME      LEAF       EMPNO      MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

Recursive WITH clause does not offer a built-in function that provides this information. Easiest way to get the information is using a subquery (in-/outside the WITH clause) that counts the number of child nodes.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename
     , 1 - LEAST((SELECT COUNT(*) FROM emp x WHERE x.mgr = e.empno),1) leaf
     , e.empno, e.mgr
  FROM emps e
 ORDER BY e.sorting
/

LEVEL      ENAME       LEAF      EMPNO       MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

If the SEARCH clause uses DEPTH_FIRST, the analytic function LEAD can be used to determine whether the column LVL of next row in the result set has a higher value than the current row. If so, the current row is not a leaf otherwise the current row is a leaf.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sortingSELECT lvl
      ,ename
      ,CASE
          WHEN lvl - LEAD(lvl) OVER (ORDER BY sorting) < 0 
             THEN 0 
          ELSE 1 
       END AS leaf 
     ,empno
     ,mgr 
  FROM emps 
 ORDER BY sorting 
/ 

LVL        ENAME      LEAF       EMPNO      MGR 
---------- ---------- ---------- ---------- ---------- 
         1 KING                0       7839 
         2 CLARK               0       7782       7839 
         3 MILLER              1       7934       7782

Recursive WITH clause (3)

03 Thursday Nov 2011

Posted by troroblog in ORACLE 11g Release 2

≈ Leave a comment

Tags

CONNECT BY, ORACLE, RECURSION, SQL

How to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.


Cycle Detection

If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.

UPDATE emp
   SET mgr = 7499
 WHERE empno = 7839
/

SELECT LEVEL, e.ename
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.empno = 7839
 ORDER SIBLINGS BY e.ename
/

ERROR:
ORA-01436: CONNECT BY loop in user data

Recursive suquery factoring throws a different error code with a similar message.

WITH r_emps (ename, empno, mgr, lvl, path, root)
         AS (SELECT e.ename, e.empno, e.mgr
                  , 1 as lvl
                  , '/' || e.ename AS path
                  , e.ename        AS root
               FROM emp e
              WHERE e.empno = 7839
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
                  , m.lvl + 1                AS lvl
                  , m.path || '/' || e.ename AS path
                  , m.root
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename, e.empno, e.mgr, e.path, e.root
  FROM r_emps e
 ORDER BY e.sorting
/

ERROR at line ..:
ORA-32044: cycle detected while executing recursive WITH query

NOCYLCE

Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles and detect nodes with a connection to a node already included in the result set (cycle).

SELECT  LEVEL
      , CONNECT_BY_ISCYCLE                AS CYCLE
      , e.ename
      , SYS_CONNECT_BY_PATH(e.ename, '/') AS path 
      , e.mgr
  FROM emp e
CONNECT BY NOCYCLE PRIOR e.empno = e.mgr
 START WITH e.empno = 7839
 ORDER SIBLINGS BY e.ename
/

LEVEL  CYCLE  ENAME     PATH                    MGR
-----  -----  --------  ---------------------  ----
    1  0      KING     /KING                   7499
    2  0      BLAKE    /KING/BLAKE             7839
    3  1      ALLEN    /KING/BLAKE/ALLEN       7698
    3  0      JAMES    /KING/BLAKE/JAMES       7698
    3  0      MARTIN   /KING/BLAKE/MARTIN      7698
    3  0      TURNER   /KING/BLAKE/TURNER      7698
    3  0      WARD     /KING/BLAKE/WARD        7698
    2  0      CLARK    /KING/CLARK             7839
    3  0      MILLER   /KING/CLARK/MILLER      7782
    2  0      JONES    /KING/JONES             7839
    3  0      FORD     /KING/JONES/FORD        7566
    4  0      SMITH    /KING/JONES/FORD/SMITH  7902
    3  0      SCOTT    /KING/JONES/SCOTT       7566

The cycle was detected at node ALLAN (CONNECT_BY_ISCYCLE returned the value 1) and further processing of this branch is stopped.
The recursive WITH clause also offers a clause to handle cycles.

WITH r_emps (ename, empno, mgr, lvl, path)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl, '/' || e.ename AS path
               FROM emp e
              WHERE e.empno = 7839
             UNION ALL
     SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl, m.path || '/' || e.ename AS path
               FROM            r_emps m 
                    INNER JOIN emp    e ON (e.mgr = m.empno))
SEARCH DEPTH FIRST BY ename SET SORTING
CYCLE empno SET is_cycle TO 1 DEFAULT 0
SELECT e.lvl AS LEVEL, is_cycle AS CYCLE, e.ename, e.path ,e.mgr
  FROM r_emps e
 ORDER BY e.sorting
/

LEVEL  CYCLE  ENAME      PATH                      MGR
-----  -----  ---------  -----------------------  ----
    1      0  KING       /KING                    7499
    2      0  BLAKE      /KING/BLAKE              7839
    3      0  ALLEN      /KING/BLAKE/ALLEN        7698
    4      1  KING       /KING/BLAKE/ALLEN/KING   7499
    3      0  JAMES      /KING/BLAKE/JAMES        7698
    3      0  MARTIN     /KING/BLAKE/MARTIN       7698
    3      0  TURNER     /KING/BLAKE/TURNER       7698
    3      0  WARD       /KING/BLAKE/WARD         7698
    2      0  CLARK      /KING/CLARK              7839
    3      0  MILLER     /KING/CLARK/MILLER       7782
    2      0  JONES      /KING/JONES              7839
    3      0  FORD       /KING/JONES/FORD         7566
    4      0  SMITH      /KING/JONES/FORD/SMITH   7902
    3      0  SCOTT      /KING/JONES/SCOTT        7566
    4      0  ADAMS      /KING/JONES/SCOTT/ADAMS  7788

The difference between CONNECT BY and RECURSIVE WITH cycle detection is that with RECURSIVE WITH the cycle is decteted after the next recursion level wass processed. The erroneous node is repeated and the cycle flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.

Recursive WITH clause (2)

19 Monday Sep 2011

Posted by troroblog in ORACLE 11g Release 2

≈ Leave a comment

Tags

CONNECT BY, ORACLE, RECURSION, SQL

How to implement CONNECT BY features

 ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

Root and Path

CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy:

SELECT e.ename, e.empno, e.mgr
      ,SYS_CONNECT_BY_PATH(e.ename,'/') AS path
      ,CONNECT_BY_ROOT e.ename          AS root
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

ENAME    EMPNO   MGR  PATH                     ROOT
-------  -----  ----  -----------------------  ----
KING      7839        /KING KING               
BLAKE     7698  7839  /KING/BLAKE              KING
ALLEN     7499  7698  /KING/BLAKE/ALLEN        KING
JAMES     7900  7698  /KING/BLAKE/JAMES        KING
MARTIN    7654  7698  /KING/BLAKE/MARTIN       KING
TURNER    7844  7698  /KING/BLAKE/TURNER       KING
WARD      7521  7698  /KING/BLAKE/WARD         KING
CLARK     7782  7839  /KING/CLARK              KING
MILLER    7934  7782  /KING/CLARK/MILLER       KING
JONES     7566  7839  /KING/JONES              KING
FORD      7902  7566  /KING/JONES/FORD         KING
SMITH     7369  7902  /KING/JONES/FORD/SMITH   KING
SCOTT     7788  7566  /KING/JONES/SCOTT        KING
ADAMS     7876  7788  /KING/JONES/SCOTT/ADAMS  KING

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr, path, root)
         AS (SELECT e.ename, e.empno, e.mgr
                  , '/' || e.ename AS path
                  , e.ename        AS root
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
                  , m.path || '/' || e.ename AS path
                  , m.root
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.ename, e.empno, e.mgr, e.path, e.root
  FROM r_emps e
 ORDER BY e.sorting
/

Path and root are set to ename in the anchor query. While the path is enlarged along the hierarchy by adding separator and path the root is simply passed all the way down to the leafs.

Recursive WITH clause (1)

19 Monday Sep 2011

Posted by troroblog in ORACLE, ORACLE 11g Release 2, SQL, Uncategorized

≈ Leave a comment

Tags

CONNECT BY, ORACLE, ORACLE 11g Release 2, RECURSION, SQL

how to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entries show how to implement all the CONNECT BY features using the new recursive WITH clause.


Basic recursive query

CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):

SELECT e.ename, e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr)          AS (SELECT e.ename, e.empno, e.mgr
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT e.ename, e.empno, e.mgr
  FROM r_emps e
/

The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the
anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.


Formatting the result set

CONNECT BY queries often use the LEVEL pseudo column to format the output.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

The same can be achieved using the following recursive WITH query:

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
            SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
      ,e.empno, e.mgr
  FROM r_emps e
/

To simulate the LEVEL pseudo column using the recursive WITH clause, one has to add a virtual level column on the root elements, which will be incremented along the hierarchy.


Sorting the result set

The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

Same result can be achieved using the recursive WITH clause with the SEARCH clause.

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
     , e.empno ,e.mgr
  FROM r_emps e
 ORDER BY e.sorting
/

SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children, the ename column is used as the order attribute within the hierarchy and assigned to the attribute sorting which can afterwards be used in the outermost query.

Working with a single date validity column

08 Monday Aug 2011

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

DATE, ORACLE, SQL, VALIDITY

Having a single column to determine the validity of a value has many advantages over a two column (valid_from / valid_to) approach. 

  • no need to care about gaps 
  • no need to care about overlaps
  • simple way to prevent duplicates possible (unique constraint)

Therefore this is often seen in parameter tables or any other tables storing different versions of an information with a time range validity.

The problem occurs when you try to use the correct information in your queries.


Prepare test case:

Our test case has an application parameter table (key,value,valid_from) holding information about the tax rate of the swiss vat.

CREATE TABLE appl_params (
    param_name VARCHAR2(20)  NOT NULL
   ,param_value       VARCHAR2(200) NOT NULL
   ,param_valid_from  DATE          NOT NULL)
/

A simple primary key on param_name, param_valid_from is sufficient to avoid duplicate validities for one parameter at a point in time.

ALTER TABLE appl_params 
   ADD CONSTRAINT appl_params_pk 
   PRIMARY KEY (param_name, param_valid_from)
/
INSERT INTO appl_params VALUES('SWISS VAT','7.6',TO_DATE('01.01.2005','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','8.0',TO_DATE('01.01.2011','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','7.0',TO_DATE('01.01.2014','dd.mm.yyyy'));
COMMIT;

To get the correct tax rate for a sale we need to find the rate belonging to the valid_from which is the latest before the sale takes place…. This is a rather complicated query.

WITH sales (prod, sale_date, price)
        AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2015-07-27', 115 FROM dual)
 SELECT prod
      , sale_date
      , price
      , TO_NUMBER(ap.param_value)
      , price + (price * TO_NUMBER(ap.param_value)/100) AS price_incl_vat
   FROM sales s
       ,appl_params ap
  WHERE ap.param_name = 'SWISS VAT'
    AND ap.param_valid_from = (SELECT MAX(ap2.param_valid_from)
                                 FROM appl_params ap2
                                WHERE ap2.param_name = ap.param_name
                                  AND ap2.param_valid_from <= s.sale_date) 
/

To support easier and more intuitive queries we create a view which has an additional column param_valid_to. To populate this column we use the analytic function lead.

The solution underneath assumes that param_valid_to as well as sale_date do not include relevant time information (all date columns are truncated to midnight).

CREATE VIEW appl_params_vw
AS
SELECT param_name, param_value, param_valid_from
     , LEAD(param_valid_from,1,date '3000-01-01') 
             OVER (PARTITION BY param_name
  ORDER BY param_valid_from ASC) - 1 AS param_valid_to
  FROM appl_params             
/

After having created this view, the query to find the appropriate tax rate gets quite easy.

WITH sales (prod, sale_date, price)
        AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2015-07-27', 115 FROM dual)
SELECT s.prod
     , s.sale_date
     , s.price
     , TO_NUMBER(v.param_value) vat
     , s.price + (s.price * to_number(v.param_value)/100) AS price_incl_vat
  FROM            sales          s 
       INNER JOIN appl_params_vw v ON (    v.param_name = 'SWISS VAT'
                                       AND s.sale_date BETWEEN v.param_valid_from 
                                                           AND v.param_valid_to)
/

If the approach is not fast enough we would also have the possibility to materialize this view which is, for a table like the parameter table where changes do not happen to often, not a bad idea.

← Older posts
Newer posts →

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