Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring

Lucas Jellema 1
0 0
Read Time:3 Minute, 49 Second

 

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

 

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring

  1. Hi,
    How would you use the above with the following use-case:
    I need to return a full tree structure where a like match is done on a name column. The data would look like
    manId | empID | name
    1 Paul
    1 2 Kara
    1 3 Matthew
    1 4 Bill
    2 5 Gary
    2 6 Paul
    3 7 Sally
    3 8 Sarah
    4 9 Jill
    4 10 Sarah
     
    so the strucure is
     
    – 1. Paul
    – 2. Kara
    – 5. Gary
    – 6. Paul
    – 3. Matthew
    – 7. Sally
    – 8. Sarah
    – 4. Bill
    – 9. Jill
    – 10. Sarah
     
    and I’d like to be able to search for where lower(name) like ‘%sarah%’ and have the following returned:
     
    – 1. Paul
    – 3. Matthew
    – 8. Sarah
    – 4. Bill
    – 10. Sarah
     
    likewise I’d like to be able to search where lower(name) like ‘%matthew%’ and have the following returned:
     
    – 1. Paul
    – 3. Matthew
    – 7. Sally
    – 8. Sarah
     
    Possible?

Comments are closed.

Next Post

Oracle Open World XMLDB Sessions and Presentations

Its time to enlist. It really is. At least regarding the XMLDB related sessions and presentations during Oracle Open World 2009. As far as I have seen today, the hands-on workshop has almost no seats left and more than half already enlisted for my XMLDB New Features and XMLDB Performance related presentation.Oracle Open World doesn't have much XMLDB related sessions, until now from the 1800+ sessions, I only counted a dozen or so. If you want to have a peak of the ones I found, have a look at the following Oracle OTN XMLDB Forum thread.Oracle Open World 2009: XMLDB Presentations and Related Workshops I enlisted for most, as long as they didn't interfere with my presentation schedule, if not only that I am very anxious about Mark Drake's presentations about Google API mashups etc combined with the XMLDB functionality in the database and the, probably extended version of how to build an APEX content management system based on the versioning and filler capabilities of the XMLDB Repository. During OOW 2008, Carl Backstrom, helped and co-presented this APEX/XMLDB joined venture and, alas, this demo app of the XFILES light weight XMLDB CMS, never (at least yet AFAIK) hit the apex.oracle.com demogrounds or the OTN XMLDB main pages.read more...
%d bloggers like this: