Skip to Main Content
  • Questions
  • Recursion - Why is anchor value not repeated

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, mac.

Asked: January 19, 2023 - 12:26 am UTC

Last updated: January 19, 2023 - 1:37 pm UTC

Version: 2023 Oracle ยท Live SQL 23.1.1, running Oracle Database 19c EE Extreme Perf - 19.17.0.0.0

Viewed 1000+ times

You Asked

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.



with LiveSQL Test Case:

and Chris said...

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


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.