0

I have self referenced table - HIERARCHY(id, name, parent_id). So I need to get all the hierarchy by any node of this hierarchy. For example we have tree, where h1, h2 are roots:

-(h1)
  | |_(h1_1)
  | |   |_(h1_1_2)
  | |_(h1_2)
  |    |_(h1_2_1)
-(h2)
  | |_(h2_1)
  | |_(h2_2)   
  |

What I need, it to get all the tree e.g with root h1 by any node of this tree e.g. by h1_2

     -(h1)
        |_(h1_1)
get     |   |_(h1_1_2)    by h1_2 or h1_2_1, etc
        |_(h1_2)
           |_(h1_2_1)

I wrote the query:

WITH RECURSIVE hierarchy_with_parents(id) AS (
  SELECT l.id, l.name, l.parent_id FROM hierarchy AS l WHERE l.id = <any_row_id>
  UNION ALL
  SELECT lc.id, lc.name, lc.parent_id FROM hierarchy lc, hierarchy_with_parents lwp WHERE lc.id = lwp.parent_id
), hierarchy_with_children(id) AS (
  SELECT l.id, l.name, l.parent_id FROM hierarchy AS l WHERE l.id 
  IN ( -- sub-query for getting parent id
    SELECT
    lwp.id
    FROM hierarchy_with_parents AS lwp
    WHERE lwp.parent_id IS NULL
  )
  UNION ALL
  SELECT lc.id, lc.name, lc.parent_id FROM hierarchy lc, hierarchy_with_children lwc WHERE lc.parent_id = lwc.id
)
SELECT * FROM hierarchy_with_children

hierarchy_with_parents - returns subtree from child to parent(inclusive),
hierarchy_with_children - returns all tree.

It seems all works Ok, but I am not DB-expert and I want to know limitations and comments about my query. Also any other solutions for PostgreSQL and Oracle 11g welcome.

Thanks.

Sergey Morozov
  • 4,528
  • 3
  • 25
  • 39
  • For limitation of recursion see http://stackoverflow.com/questions/32096103/selecting-n-rows-in-sql-server – Esty Oct 10 '16 at 11:24
  • 1
    @TanjimRahman: that question is about SQL Server, those limits don't apply in Postgres or Oracle –  Oct 10 '16 at 12:43
  • 1
    OK, some inept user marked this as belonging on DBA SE. I am curious why. Even if the application is about administering one's DB (and it may very well not be), the question is about specific SQL code to solve it, it is not about administering the DB. –  Oct 10 '16 at 13:19
  • 1
    Limitations of recursive CTE's in Oracle (the ones I am aware of) have to do with Oracle version: they are available only in Oracle 11.1 and above, and having the column names defined right after the CTE name is available only from Oracle 11.2 on (if I remember correctly). There were (and still are) various bugs, some fixed, some not, but if the query works, you haven't run into any of them. With that said, for proper hierarchical problems like this one the old, "standard" queries (with CONNECT BY and the like) are likely more efficient and have fewer bugs, so I prefer them. –  Oct 10 '16 at 14:44

1 Answers1

1

Given :input_node, first find the root of the subtree containing that node. This is done in the find_root factored subquery. Then simply collect all the rows that link to that root. I need the NVL() call in the outer query in case the :input_node was a root already; in that case find_root returns no rows. When used as a scalar subquery, that is treated as null (at least in Oracle), so I can use NVL() to fix it.

with
     hierarchy as (
       select 2 as child, 1  as parent from dual union all
       select 3, 2 from dual union all
       select 4, 1 from dual union all
       select 5, 4 from dual union all
       select 7, 6 from dual union all
       select 8, 7 from dual union all
       select 9, 6 from dual
     ),
     find_root as (
       select parent as rt
       from hierarchy
       where connect_by_isleaf = 1       
       start with child = :input_node
       connect by child = prior parent
     ) 
select child, parent
from   hierarchy
start with parent = nvl((select rt from find_root), :input_node)
connect by parent = prior child;