Oracle - Loop through hierarchised records

Question

So, since I struggled to find an accurate title, I think a detailled shema will be much more understandable.

I have this table PROGRAM that I will reduce to 3 fields for simplicity:

ID |NAME    |ID_ORIGINAL_PROGRAM
1  |name_1  |
2  |name_2  |1
3  |name_3  |1
4  |name_4  |2
5  |name_5  |3      
6  |name_6  |
7  |name_7  |6

I'm trying to find a query that will allow me, with any ID as parameter to gather all the related programs to this id. And I need to be able to send a parameter than does not necessarily has to be the "father" id of the hierarchy.

For example, if parameter ID is 1, then results will be:

If parameter ID is 4, then the results will be:

It seems like I'm missing some kind "loop" logic that I can't clearly identify. I looked up at "CONNECT BY PRIOR" but was not able to grasp the concept enough to understand how to deploy it.

Edit: So it seems I found a way through:

    SELECT ID
      FROM PROGRAM
START WITH ID = 67256
CONNECT BY NOCYCLE ID_ORIGINAL_PROGRAM = PRIOR ID
                   OR ID = PRIOR ID_ORIGINAL_PROGRAM
order by ID

I'm a bit concerned by the performances though (it takes 1 second to perform)

CONNECT BY allows us to walk down a tree (parent>child>grandchild) or up a tree (child>parent>grandparent) but not both. There's no way of handling your *parameter ID is 4* case in a single SQL statement. In fact `4` is not hierarchically related to either `3` or `5` are related to `4`. They are "cousins" not parents or children. If you want to map relationships like that you need a Graph database (Oracle can do graphs through [the Spatial and Graph option](https://www.oracle.com/database/technologies/spatialandgraph.html), but that is a chargeable extra.) — APC, Jun 20 '19 at 16:09
if you need to read it more vs inserting/updating [Nested set model](https://en.wikipedia.org/wiki/Nested_set_model) is a much better approach then the adjacency list which you are using.. — Raymond Nijland, Jun 20 '19 at 18:12

Barbaros Özhan · Answer 1 · 2019-06-20T18:06:19.920

I suppose you need

with program( id, id_original_program ) as
(
 select 1, null from dual union all
 select 2, 1    from dual union all
 select 3, 1    from dual union all
 select 4, 2    from dual union all
 select 5, 3    from dual union all
 select 6, null from dual union all
 select 7, 6    from dual 
)
 select id, sys_connect_by_path(id, '->') as path
   from program
  where id_original_program is not null 
 connect by prior id =  id_original_program
 start with id = 1 -- 4
 order by id;

ID  PATH
2   ->1->2
3   ->1->3
4   ->1->2->4
5   ->1->3->5

if value 4 is substituted, then you get

ID  PATH
4   ->4

only.

Whether you substitute 1 or 4, you'll get the same result for your query.

Oracle - Loop through hierarchised records

1 Answers1