In the
Hierarchical Queries: Databases for Developers first recursion example we run the query for the initial case in the recursive loop:
select employee_id, first_name, last_name, manager_id
from employees
where manager_id is null
Before a union all:
union all
Before then running the part that will repeat until it hits it's termination condition:
select e.employee_id, e.first_name, e.last_name, e.manager_id
from org_chart oc
join employees e
on e.manager_id = oc.employee_id
)
My question is:
Why is the initial anchor value not repeated? Is it not searching for cases where manager id is null for every line?
I would have expected the root to be repeated between every other entry of the data.
Why would it reappear? Each hierarchy only has one root row.
Is it not searching for cases where manager id is null for every line?No!
The anchor query finds all the root rows. So this finds the rows for employees with no manager:
select employee_id, first_name, last_name, manager_id
from employees
where manager_id is null
For simplicity assume that this only returns one row.
The recursive query then joins the
current row in the hierarchy to the next.
So after fetching the root row, the current row for ORG_CHART in this query is the root:
select e.employee_id, e.first_name, e.last_name, e.manager_id
from org_chart oc
join employees e
on e.manager_id = oc.employee_id
The recursive query joins this to employees to find the rows for people who are managed by the root employee's row. These
direct reports are now the current rows in the hierarchy and the current rows for ORG_CHART. So the database then continues to follow the tree, using the rows at depth two.
The root row is already processed at this point and will not be revisited*, thus can't reappear in the output. This process continues until all the rows are fetched.
* (assuming there are no loops in your data!)
If you want to display values from the root row on every row you can; you just have to select it:
with org_chart (
employee_id, first_name, last_name, manager_id,
lvl, root_emp
) as (
select employee_id, first_name, last_name, manager_id, 1,
employee_id
from hr.employees
where manager_id is null
union all
select e.employee_id, e.first_name, e.last_name, e.manager_id,
lvl + 1, oc.root_emp
from org_chart oc
join hr.employees e
on e.manager_id = oc.employee_id
) search depth first by employee_id set seq
select * from org_chart
order by seq
fetch first 15 rows only;
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER_ID LVL ROOT_EMP SEQ
----------- -------------------- ------------------------- ---------- ---------- ---------- ----------
100 Steven King <null> 1 100 1
101 Neena Kochhar 100 2 100 2
108 Nancy Greenberg 101 3 100 3
109 Daniel Faviet 108 4 100 4
110 John Chen 108 4 100 5
111 Ismael Sciarra 108 4 100 6
112 Jose Manuel Urman 108 4 100 7
113 Luis Popp 108 4 100 8
200 Jennifer Whalen 101 3 100 9
203 Susan Mavris 101 3 100 10
204 Hermann Baer 101 3 100 11
205 Shelley Higgins 101 3 100 12
206 William Gietz 205 4 100 13
102 Lex De Haan 100 2 100 14
103 Alexander Hunold 102 3 100 15