You Asked
Hi Tom,
I am working on a performance issue of a query using CYCLE clause and I'm looking online and docs but i still don't understand what it does. Perhaps you can help shed some light on this one:
WITH SUPRV_DETS
AS (SELECT b.dr_grpid,
a.emp_id,
a.supv_emp_id,
a.effective_date effective_From,
a.effective_to
FROM LINE_SUPERVISOR_HIERARCHY a, DIRECT_REPORT_GRP b
WHERE a.emp_id = b.line_supv_emp_id
AND a.effective_TO >= b.first_use_date
AND a.emp_id <> -1
),
SUPRV_CHG_DETS (dr_grpid,
root_effective_from,
root_effective_to,
emp_id,
supv_emp_id,
effective_From,
effective_to)
AS (SELECT dr_grpid,
effective_From root_effective_from,
effective_to root_effective_to,
emp_id,
supv_emp_id,
effective_From,
effective_to
FROM SUPRV_DETS
UNION ALL
SELECT ssd.dr_grpid,
ssd.root_effective_from,
ssd.root_effective_to,
sc.emp_id,
sc.supv_emp_id,
sc.effective_date effective_From,
sc.effective_to
FROM LINE_SUPERVISOR_HIERARCHY sc, SUPRV_CHG_DETS ssd
WHERE sc.emp_id <> -1
AND sc.emp_id = ssd.supv_emp_id
AND sc.effective_date <= ssd.root_effective_to
AND sc.effective_to >= ssd.root_effective_from)
CYCLE emp_id SET IS_CYCLE TO 'Y' DEFAULT 'N'
SELECT DISTINCT dr_grpid, effective_From
FROM SUPRV_CHG_DETS
WHERE effective_From BETWEEN root_effective_from AND root_effective_to
and Chris said...
The CYCLE clause enables you to detect loops in the data.
For example, this hierarchy has a loop in it. Wherever you start, eventually you'll end up back at that row:
with rws as (
select level par, level + 1 chd from dual
connect by level <= 5
union all
select 6 par, 1 chd from dual
)
select * from rws;
PAR CHD
1 2
2 3
3 4
4 5
5 6
6 1 So if you try and build a tree using it, you'll get:
with rws as (
select level par, level + 1 chd from dual
connect by level <= 5
union all
select 6 par, 1 chd from dual
), tree ( par, chd ) as (
select par, chd
from rws
where par = 1
union all
select r.par, r.chd
from tree t
join rws r
on t.chd = r.par
) cycle par set is_loop to 'Y' default 'N'
select * from tree;
ORA-32044: cycle detected while executing recursive WITH query
Adding the CYCLE clause enables the database to spot this, stop processing, and flag the offending row:
with rws as (
select level par, level + 1 chd from dual
connect by level <= 5
union all
select 6 par, 1 chd from dual
), tree ( par, chd ) as (
select par, chd
from rws
where par = 1
union all
select r.par, r.chd
from tree t
join rws r
on t.chd = r.par
) cycle par set is_loop to 'Y' default 'N'
select * from tree;
PAR CHD IS_LOOP
1 2 N
2 3 N
3 4 N
4 5 N
5 6 N
6 1 N
1 2 Y Notice how the row (1,2) appears twice? But the second time IS_LOOP is set to 'Y'?
This happens when looking at the cycle column values. If it finds a row where an ancestor has the same values for these columns, it stops.
This enables the database to spot another data anomalies, such as a node with two parents. For more details, see this video: