Oracle 使用CYCLE子句查询

嗨，汤姆，

我正在使用CYCLE子句处理查询的性能问题，我正在寻找在线和文档，但我仍然不明白它的作用。也许你可以帮助阐明这一点:

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

CYCLE子句使您能够检测数据中的循环。

例如，此层次结构中有一个循环。无论你从哪里开始，最终你都会回到那一行:

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 

所以如果你试着用它来建造一棵树，你会得到:

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

添加CYCLE子句使数据库能够发现这一点，停止处理，并标记有问题的行:

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   

注意行 (1,2) 如何出现两次？但是第二次IS_LOOP设置为 'Y'？

当查看循环列值时，会发生这种情况。如果它找到祖先对这些列具有相同值的行，它将停止。

这使数据库能够发现另一个数据异常，例如具有两个父节点的节点。有关更多详细信息，请参见以下视频: