Search notes:

Oracle SQL: hierarchical queries / start with … connect by nocycle

A directed graph with cycles

Here's a directed graph with cycles

A-->C<--D-->F-->L
|   |   ^   |   ^
v   v   |   v   |
B   H-->I   J-->K
|           |   |
v           v   v
E           G   M

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/directed-graph.txt

Create table

In order to store the graph in Oracle, we create a table:

create table directed_graph (
  node_from char(1),
  node_to   char(1)
);

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/create-table.sql

Inserting edges

We insert a record for each edge:

insert into directed_graph values ('A', 'C');
insert into directed_graph values ('A', 'B');
insert into directed_graph values ('B', 'E');
insert into directed_graph values ('C', 'H');
insert into directed_graph values ('H', 'I');
insert into directed_graph values ('I', 'D');
insert into directed_graph values ('D', 'F');
insert into directed_graph values ('D', 'C');
insert into directed_graph values ('F', 'J');
insert into directed_graph values ('J', 'K');
insert into directed_graph values ('J', 'G');
insert into directed_graph values ('K', 'L');
insert into directed_graph values ('F', 'L');
insert into directed_graph values ('K', 'M');

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/insert-edges.sql

Nodes reachable from F

The following query selects all nodes that are reachable from F:

select
  lpad(' ', level-1) || node_from || '->' || node_to
from
  directed_graph
start with
  node_from = 'F'
connect by
  prior node_to = node_from;

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/nodes-reachable-from-F.sql

The query returns

F->J
 J->G
 J->K
  K->L
  K->M
F->L

Note: the node L is reached via two paths. This is not a problem for the select statement.

Nodes reachable from A

Similar query, but now starting from node A:

select
  lpad(' ', level-1) || node_from || '->' || node_to
from
  directed_graph
start with
  node_from = 'A'
connect by
  prior node_to = node_from;

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/nodes-reachable-from-A.sql

Because there's a loop (C->H->D->C), Oracle will throw the error ORA-01436: CONNECT BY loop in user data.

In order to prevent this error, we'll use the nocycle clause:

select 
  lpad(' ', level-1) || node_from || '->' || node_to
from
  directed_graph
start with
  node_from = 'A'
connect by nocycle
  prior node_to = node_from;

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/nodes-reachable-from-A-nocycle.sql

The query returns

A->B
 B->E
A->C
 C->H
  H->I
   I->D
    D->F
     F->J
      J->G
      J->K
       K->L
       K->M
     F->L

The query works now. However, the edge D->C is not displayed!

Cleaning up

Finally, we can clean up:

drop table directed_graph purge;

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/nocycle/clean-up.sql