Find cycles in directed graph using MODEL clause

Question

I have a table with two columns U and V, where row with values ('a', 'b') means that there is an edge from vertex 'a' to 'b'.

I can easily find cycles in graph using hierarchical query or recursive WITH, but I just can't wrap my head around MODEL clause and how find cycles using it.

So, the question is:

1. How to find cycles in directed graph using MODEL clause?
2. (OR) How to implement behaviour, similar to hierarchical queries or recursive WITH, using MODEL clause?

For table:

   U   |   V      
----------------
   a   |   b
   b   |   c
   c   |   a
   g   |   h
   h   |   g
      ...

Result should look something like this:

CYCLES
------
a->b->c->a
g->h->g
...

Answer 1

Model was designed for spreadsheet-like calculations and not for traversing graphs.

For your data you can use below solution and it returns correct result only because there is "data magic" - each child is greater than its parent.

column path format a15
with dag(u, v) as (
select 'a', 'b' from dual union all
select 'b', 'c' from dual union all
select 'b', 'k' from dual union all
select 'c', 'a' from dual union all
select 'g', 'h' from dual union all
select 'h', 'g' from dual)
select u, v, path || '->' || v path, instr(path, v) is_cycle
from (select t.*, case when u in ('a', 'g') then u end path from dag t)
model
dimension by (u, v)
measures (cast(path as varchar2(4000)) path, 0 cycle)
(
  path[any, any] order by u, v =
  nvl(path[cv(u), cv(v)], max(path)[any, cv(u)] || '->' || cv(u))
)
order by 1, 2;

U V PATH              IS_CYCLE
- - --------------- ----------
a b a->b                     0
b c a->b->c                  0
b k a->b->k                  0
c a a->b->c->a               1
g h g->h                     0
h g g->h->g                  1

6 rows selected.

In general case you can use iterative model to imitate breadth-first search and on each iteration populate path for nodes which are children (and have not been visited yet) for nodes marked on previous iteration.