Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring
Oracle Database 11g Release 2 introduces the successor to the good old Connect By based hierarchical querying, called Recursive Subquery Factoring. The basics are described in a previous article: http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it. This article will show some additional examples of using this recursive subquery factoring syntax.
The essence of this recursiveness: the subquery consists of two queries unioned together. The first query returns the root-nodes, the starting points in the tree or network. The second query is used to continually retrieve the next step or level: it refers to the subquery itself and finds the next node level based on the levels (root and zero or more previously retrieved levels) already retrieved by the subquery.
with employees (empno, name, mgr) as ( select empno, ename, mgr from emp where mgr is null union all select e.empno, e.ename , e.mgr from emp e join <strong>employees</strong> m on (m.empno = e.mgr) ) select * from employees
Here we see a subquery with two queries unioned together. The first query retrieves the root nodes – the Employees for which mgr is not set. The second query joins table EMP with the result from the recursive subquery itself (employees). The first iteration of this second query retrieves the EMP records for which the MGR value is equal to the EMPNO value in one of the root nodes. The second iteration will add the EMP records for which the MGR value is equal to the EMPNO value in one of the nodes returned by the first iteration. Additional iterations can add more records..
The relatively recent Oracle 10g function CONNECT_BY_ROOT can be used in hierarchical queries to return values from the root record. When it was introduced, we considered it a fairly advanced function. When using the recursive subquery factoring, emulating that function is extremely simple and intuitive:
with employees (empno, name, mgr, hierlevel, <strong>root_ename</strong>) as ( select empno, ename, mgr, 1, <strong>ename</strong> from emp where mgr is null union all select e.empno, e.ename , e.mgr, m.hierlevel + 1 , <strong>m.root_ename</strong> from emp e join employees m on (m.empno = e.mgr) ) select ..., <strong>root_ename</strong> from employees /
As you can see, to find the name of the super-manager for every record returned in this hierarchical query, we include the value – ename – in the first query (the one that returns the root nodes) and carry that value forward in every record retrieved in the second query.
The LEVEL pseudo column that we can use with connect by is also available with the recursive subquery, in a similar way as the root node:
with employees (empno, name, mgr, <strong>hierlevel</strong>) as ( select empno, ename, mgr, <strong>1</strong> from emp where mgr is null union all select e.empno, e.ename , e.mgr, <strong>m.hierlevel + 1</strong> from emp e join employees m on (m.empno = e.mgr) ) select * from employees
Ordering records in the hierarchy
The connect by based hierarchical queries can use the ORDER SIBLINGS BY clause to determine how to order the sibling nodes under their common parent. However, using this clause prevents you from using a regular ORDER BY expression.
The new approach for hierarchical queries provides another option: without explicitly ordering the nodes, we can have the query return the sequence number of the node, either processing children before siblings, always trying to go deeper into the tree, or processing siblings before children. The former is called SEARCH DEPTH FIRST, the latter is SEARCH BREADTH FIRST. The
ordering_column that contains the sequence number is automatically added to the column list for the recursive query. In the code snippet, the recursive subquery is extended with an extra column called SEQ (that name can be set as you like) whose value is assigned by the SEARCH DEPTH FIRST clause with the sequence of the node when processing children before siblings (normal parent behavior).
with employees (empno, name, mgr) as ( select empno, ename, mgr from emp where mgr is null union all select e.empno, e.ename , e.mgr from emp e join employees m on (m.empno = e.mgr) ) <strong>search depth first by name set seq</strong> select empno, ename, <strong>seq</strong> from employees
Alternatively you can have siblings processed prior to children. Change DEPTH to BREADTH and your done.
Oracle 11gR2 SQL Language reference – Recursive With Clause
- Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
- Hierarchical query with nodes from different tables – DEPT and EMP nodes in one tree
- Subquery Factoring in Oracle 11g
- Selecting a 'pruned tree' with selected nodes and all their ancestors – Hierarchical SQL Queries and Bottom-Up Trees
- Calling a PL/SQL Function at the wrong time gets me into trouble – the dreaded ORA-0113 End of Comunication Channel with a hierarchical query
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation
- AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december