Recursive queries using WITH, CONNECT BY and graph data: some examples
10/11/20 07:48
We will compare result on a small graph using different options.
Here the very basic graph we will use as example:
with edges(id, nodea, nodeb) as (
select 1, 'A', 'B' from dual union all
select 2, 'B', 'C' from dual union all
select 3, 'A', 'C' from dual union all
select 4, 'C', 'B' from dual union all
select 5, 'C', 'D' from dual union all
select 6, 'D', 'E' from dual union all
select 7, 'E', 'F' from dual union all
select 8, 'F', 'A' from dual -- union all
),
parcours(lvl, id, nodea, nodeb, path) as (
select 1, id, nodea, nodeb, '/' || nodea || '/' || nodeb from edges
where id=1
union all
select p.lvl+1, e.id, e.nodea, e.nodeb, path || '/' || e.nodeb from parcours p
join edges e on p.nodeb = e.nodea
)
cycle id /* node node b */ set iscycle to 1 default 0
select * from parcours ;
|
If we use id in the cycle instruction:
1
| 1
| A
| B
| /A/B
| 0
|
2
| 2
| B
| C
| /A/B/C
| 0
|
3
| 4
| C
| B
| /A/B/C/B
| 0
|
3
| 5
| C
| D
| /A/B/C/D
| 0
|
4
| 2
| B
| C
| /A/B/C/B/C
| 1
|
4
| 6
| D
| E
| /A/B/C/D/E
| 0
|
5
| 7
| E
| F
| /A/B/C/D/E/F
| 0
|
6
| 8
| F
| A
| /A/B/C/D/E/F/A
| 0
|
7
| 1
| A
| B
| /A/B/C/D/E/F/A/B
| 1
|
7
| 3
| A
| C
| /A/B/C/D/E/F/A/C
| 0
|
8
| 4
| C
| B
| /A/B/C/D/E/F/A/C/B
| 0
|
8
| 5
| C
| D
| /A/B/C/D/E/F/A/C/D
| 1
|
9
| 2
| B
| C
| /A/B/C/D/E/F/A/C/B/C
| 1
|
If we use nodea in the cycle instruction:
1
| 1
| A
| B
| /A/B
| 0
|
2
| 2
| B
| C
| /A/B/C
| 0
|
3
| 5
| C
| D
| /A/B/C/D
| 0
|
3
| 4
| C
| B
| /A/B/C/B
| 0
|
4
| 6
| D
| E
| /A/B/C/D/E
| 0
|
4
| 2
| B
| C
| /A/B/C/B/C
| 1
|
5
| 7
| E
| F
| /A/B/C/D/E/F
| 0
|
6
| 8
| F
| A
| /A/B/C/D/E/F/A
| 0
|
7
| 3
| A
| C
| /A/B/C/D/E/F/A/C
| 0
|
7
| 1
| A
| B
| /A/B/C/D/E/F/A/B
| 0
|
If we use nodeb in the cycle instruction:
1
| 1
| A
| B
| /A/B
| 0
|
2
| 2
| B
| C
| /A/B/C
| 0
|
3
| 5
| C
| D
| /A/B/C/D
| 0
|
3
| 4
| C
| B
| /A/B/C/B
| 1
|
4
| 6
| D
| E
| /A/B/C/D/E
| 0
|
5
| 7
| E
| F
| /A/B/C/D/E/F
| 0
|
6
| 8
| F
| A
| /A/B/C/D/E/F/A
| 0
|
7
| 3
| A
| C
| /A/B/C/D/E/F/A/C
| 1
|
7
| 1
| A
| B
| /A/B/C/D/E/F/A/B
| 1
|
And if we are using
CONNECT BY:
with edges(id, nodea, nodeb) as (
select 1, 'A', 'B' from dual union all
select 2, 'B', 'C' from dual union all
select 3, 'A', 'C' from dual union all
select 4, 'C', 'B' from dual union all
select 5, 'C', 'D' from dual union all
select 6, 'D', 'E' from dual union all
select 7, 'E', 'F' from dual union all
select 8, 'F', 'A' from dual -- union all
)
select level as lvl, id, nodea, nodeb, sys_connect_by_path(nodea, '/') as path, connect_by_iscycle as iscycle
from edges
start with id = 1
connect by nocycle prior nodeb = nodea
;
|
1
| 1
| A
| B
| /A
| 0
|
2
| 2
| B
| C
| /A/B
| 1
|
3
| 5
| C
| D
| /A/B/C
| 0
|
4
| 6
| D
| E
| /A/B/C/D
| 1
|
5
| 7
| E
| F
| /A/B/C/D/E
| 0
|
6
| 8
| F
| A
| /A/B/C/D/E/F
| 1
|
