0 
0
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
Related posts:
Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it 
Factorial in Oracle SQL – using both new Recursive Subquery and classic Connect By approach 
Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required The minimum number of statements required for Inserting Records from Two Source Tables into Four Target Tables – Introducing Multi Table Insert Gauge charts in SQL or How to produce the world's ugliest speedometers
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?