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: https://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.

In code:

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)
)
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, root_ename) as
( select empno, ename, mgr, 1, ename
  from   emp
  where  mgr is null
  union all
  select e.empno, e.ename
  ,      e.mgr, m.hierlevel + 1
  ,      m.root_ename
  from   emp e
         join
         employees m
         on (m.empno = e.mgr)
)
select ..., root_ename
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, hierlevel)
as
( select empno, ename, mgr, 1
  from   emp
  where  mgr is null
  union all
  select e.empno, e.ename
  ,      e.mgr, m.hierlevel + 1
  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)
)
search depth first by name set seq
select empno, ename, seq
from   employees

Alternatively you can have siblings processed prior to children. Change DEPTH to BREADTH and your done.

Resources

Oracle 11gR2 SQL Language reference – Recursive With Clause

Blog article Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it