logo标签列表

为什么Oracle中的connect by nocycle跟随根节点循环

sqloracle11ghierarchycyclecommon-table-expression
12

有谁知道为什么Oracle在循环发生在顶节点（根节点连接回根节点）时继续遵循超出循环的路径？更重要的是，如何防止这种情况发生？

我使用Oracle 11g Release 2（11.2）并研究分层查询。我的问题将围绕Oracle数据库SQL语言参考手册页面9-4上图9-1中的树结构展开。

我使用供应商和客户的概念创建了这棵树的表结构：

    create table t
     ( vendor       varchar2(3)
    , customer   varchar2(3)
    );
    insert into t values ( '1'  , '2'  ); 
    insert into t values ( '2'  , '3'  ); 
    insert into t values ( '2'  , '4'  ); 
    insert into t values ( '4'  , '5'  ); 
    insert into t values ( '4'  , '6'  ); 
    insert into t values ( '1'  , '7'  ); 
    insert into t values ( '7'  , '8'  ); 
    insert into t values ( '1'  , '9'  ); 
    insert into t values ( '9'  , '10' ); 
    insert into t values ( '10' , '11' ); 
    insert into t values ( '9'  , '12' ); 
    commit;

以下选择查询可以无障碍地遍历树：
    select vendor, 
           customer, 
           level, 
           connect_by_isleaf as isleaf, 
           connect_by_iscycle as iscycle, 
           connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
    from t
    connect by nocycle
          vendor=prior customer
    start with vendor='1';

给出结果：
Vendor Cust     Level   Isleaf Iscycle   Path
1        2        1        0        0   1 ~ 2
2        3        2        1        0   1 ~ 2 ~ 3
2        4        2        0        0   1 ~ 2 ~ 4
4        5        3        1        0   1 ~ 2 ~ 4 ~ 5
4        6        3        1        0   1 ~ 2 ~ 4 ~ 6
1        7        1        0        0   1 ~ 7
7        8        2        1        0   1 ~ 7 ~ 8
1        9        1        0        0   1 ~ 9
9        10       2        0        0   1 ~ 9 ~ 10
10       11       3        1        0   1 ~ 9 ~ 10 ~ 11
9        12       2        1        0   1 ~ 9 ~ 12

我随后通过向结构中添加循环使事情变得更加复杂。首先是一个供应商的记录，他们向自己销售...

    --self cycle
    insert into t values ( '4'  , '4'  );

还有一个供应商的客户是他们供应商的情况...

（注：原文中的“whos”应为“whose”，我进行了修正。）
    --ancestor cycle
    insert into t values ( '6'  , '2'  );

重新执行上述的选择查询会得到与上面相同的输出，只是第3行和第5行的Iscycle为1（路径1~2~4和1~2~4~6）。请注意，CONNECT BY命名法标记了循环的父记录而不是实际完成循环的子记录。（所以我知道4和6都回到了祖先，但我不知道是哪个祖先。）
添加两条记录会在原始树的分支之间创建一个更大的循环：
 --cycle crossing branches of tree
  insert into t values ( '6'  , '9'  ); 
  insert into t values ( '11' , '2'  );

重新执行选择查询会得到以下输出结果：
Vendor Customer Level   Isleaf   Iscycle       Path
1        2        1        0        0    1 ~ 2
2        3        2        1        0    1 ~ 2 ~ 3
2        4        2        0        1    1 ~ 2 ~ 4
4        5        3        1        0    1 ~ 2 ~ 4 ~ 5
4        6        3        0        1    1 ~ 2 ~ 4 ~ 6
6        9        4        0        0    1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        5        0        0    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        6        1        1    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        5        1        0    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        1        0        0    1 ~ 7
7        8        2        1        0    1 ~ 7 ~ 8
1        9        1        0        0    1 ~ 9
9       10        2        0        0    1 ~ 9 ~ 10
10      11        3        0        0    1 ~ 9 ~ 10 ~ 11
11       2        4        0        0    1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        5        1        0    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        5        0        1    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        6        1        0    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        6        1        1    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        2        1        0    1 ~ 9 ~ 12

输出结果与预期一致。当遇到循环时，所有周期都会被标记并停止映射。
现在是问题儿童...让我们向根节点添加一个自我循环，它与上面创建的第一个循环完全相同，只是针对节点1。
    insert into t values ( '1'  , '1'  );

这次 Oracle 在节点1检测到了循环，符合预期（第一行的Iscycle标志被设置为1）; 但是，它继续通过这个循环并构建整个树结构两次。第2至21行是第22至41行的重复，其中节点1的循环被添加到路径前面。

Vendor Customer  Level Isleaf Iscycle    Path
1        1        1        0    1      1 ~ 1
1        2        2        0    0      1 ~ 1 ~ 2
2        3        3        1    0      1 ~ 1 ~ 2 ~ 3
2        4        3        0    1      1 ~ 1 ~ 2 ~ 4
4        5        4        1    0      1 ~ 1 ~ 2 ~ 4 ~ 5
4        6        4        0    1      1 ~ 1 ~ 2 ~ 4 ~ 6
6        9        5        0    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        6        0    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        7        1    1      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        6        1    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        2        0    0      1 ~ 1 ~ 7
7        8        3        1    0      1 ~ 1 ~ 7 ~ 8
1        9        2        0    0      1 ~ 1 ~ 9
9       10        3        0    0      1 ~ 1 ~ 9 ~ 10
10      11        4        0    0      1 ~ 1 ~ 9 ~ 10 ~ 11
11       2        5        0    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        6        1    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        6        0    1      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        7        1    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        7        1    1      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        3        1    0      1 ~ 1 ~ 9 ~ 12
1        2        1        0    0      1 ~ 2
2        3        2        1    0      1 ~ 2 ~ 3
2        4        2        0    1      1 ~ 2 ~ 4
4        5        3        1    0      1 ~ 2 ~ 4 ~ 5
4        6        3        0    1      1 ~ 2 ~ 4 ~ 6
6        9        4        0    0      1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        5        0    0      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        6        1    1      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        5        1    0      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        1        0    0      1 ~ 7
7        8        2        1    0      1 ~ 7 ~ 8
1        9        1        0    0      1 ~ 9
9       10        2        0    0      1 ~ 9 ~ 10
10      11        3        0    0      1 ~ 9 ~ 10 ~ 11
11       2        4        0    0      1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        5        1    0      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        5        0    1      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        6        1    0      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        6        1    1      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        2        1    0      1 ~ 9 ~ 12

为什么1-1周期没有像4-4周期一样被处理？我错过了什么吗？

为了减轻这种情况，我在CONNECT BY子句中添加了一个额外的条件，要求客户不是“1”。

    select vendor, 
           customer, 
           level, 
           connect_by_isleaf as isleaf, 
           connect_by_iscycle as iscycle, 
           connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
    from t
    connect by nocycle
          vendor=prior customer
          and customer<>'1' 
    start with vendor='1';

具有讽刺意味的是，这一举动只是从第一行中删除了循环标志。任何帮助将不胜感激。
也许是因为1是1级，而4不是。另外，为什么不只添加WHERE VENDOR <> CUSTOMER呢？ - Conrad Frix
我想在出现循环时得到通知，因此不想使用 where vendor <> customer。我的一些实际树结构涉及10到20个级别和数千个节点。复制第一级会导致大量重复。根据我能找到的所有文档，循环发生在第一级应该没有关系。...但它确实有关系... :-( - Larry May
4个回答
1
Oracle会选择层次结构中的根行（满足START WITH条件的行）。 Oracle会选择每个根行的子行。 每个子行必须符合CONNECT BY条件相对于其中一个根行的条件。 要查找父行的子项，Oracle会评估父行的CONNECT BY条件的PRIOR表达式和表中每行的其他表达式。 条件为真的行是父行的子项。 CONNECT BY条件可以包含其他条件以进一步过滤查询所选的行。
A root row is the highest row within an inverted tree.

如果您尝试使用与子节点（22、33或44）相同的父节点，则会起作用，因为它们不是根行，只是父节点。由于1是根并且也是带有1的子节点，所以LEVEL被设置为循环，因为CONNECT_BY_ROOT子句。
输出中的重复发生是因为connect by也在重复的根上运行。
Oracle is not able to restrict the uniqueness since Oracle can't give preference to one of the other

要么使您的数据集唯一，要么编写代码以便oracle可以按照层次结构中的首选项进行操作。

跟进： 解决OP问题的方法

SELECT
      VENDOR,
      CUSTOMER,
      LEVEL,
      CONNECT_BY_ISLEAF AS ISLEAF,
      CONNECT_BY_ISCYCLE AS ISCYCLE,
      CONNECT_BY_ROOT VENDOR
      || SYS_CONNECT_BY_PATH ( CUSTOMER,
                          ' ~ ' )
          AS PATH
FROM
      (SELECT
            VENDOR,
            CUSTOMER
       FROM
            T
       WHERE
            CUSTOMER <> '1')
CONNECT BY
      NOCYCLE VENDOR = PRIOR CUSTOMER
START WITH
      VENDOR = '1';

Results:

VENDOR CUSTOMER      LEVEL     ISLEAF    ISCYCLE PATH                                                                            
------ -------- ---------- ---------- ------------------------------------------------------------------------------------------
1      2                 1          0          0 1 ~ 2                                                                           
2      3                 2          1          0 1 ~ 2 ~ 3                                                                       
2      4                 2          0          1 1 ~ 2 ~ 4                                                                       
4      5                 3          1          0 1 ~ 2 ~ 4 ~ 5                                                                   
4      6                 3          0          1 1 ~ 2 ~ 4 ~ 6                                                                   
6      9                 4          0          0 1 ~ 2 ~ 4 ~ 6 ~ 9                                                               
9      10                5          0          0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10                                                          
10     11                6          1          1 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11                                                     
9      12                5          1          0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12                                                          
1      7                 1          0          0 1 ~ 7                                                                           
7      8                 2          1          0 1 ~ 7 ~ 8                                                                       
1      9                 1          0          0 1 ~ 9                                                                           
9      10                2          0          0 1 ~ 9 ~ 10                                                                      
10     11                3          0          0 1 ~ 9 ~ 10 ~ 11                                                                 
11     2                 4          0          0 1 ~ 9 ~ 10 ~ 11 ~ 2                                                             
2      3                 5          1          0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3                                                         
2      4                 5          0          1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4                                                         
4      5                 6          1          0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5                                                     
4      6                 6          1          1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6                                                     
9      12                2          1          0 1 ~ 9 ~ 12                                                                      

20 rows selected
@reaispirituais 感谢您的回复。当您说“由于CONNECT_BY_ROOT子句，因为1是根节点也是一个子节点，所以LEVEL被设置为循环”，我不太确定您的意思。您是说如果我去掉CONNECT_BY_ROOT函数，我的问题就会消失吗？您提出的代码未能通知我存在从节点4返回节点4的循环。 :-( 另外：我随后发现，任何对根节点的引用都无法在第一次通过时触发循环标志。如果我消除任何等于根的子节点，我会破坏合法的链接。 - Larry May
当存在多个根时，请使用START WITH VENDOR IN (1, 2)（例如）{与您建议的子查询中的where子句一起，其中WHERE CUSTOMER NOT IN (1, 2)}将破坏供应商1的整个树结构分支。 - Larry May
是的，你说得对。我的观点是“Oracle 无法限制唯一性，因为 Oracle 不能偏好其中之一”，这是由于自循环根节点。我提供的是一个演示来推理你的问题，而不是解决方案。仍然需要在此方面进行工作。 - Srini V
@LarryMay 已更新了你的问题的 SQL。 - Srini V
1

我同意@realspirituals对Oracle如何处理分层数据的初始解释。在我的看法中，首先需要找到由START WITH子句指定的树的根元素。这可以重述为以下查询：

select * from t where vendor = '1';
VENDOR  CUSTOMER
------------------
1   2
1   7
1   9
1   1

实际上，我们有4个根节点和4棵独立的树。下一步是迭代地评估CONNECT BY子句。想象一下，我们拿到了上面的CUSTOMER值列表并寻找它们的后代：
select * from t where vendor in ('2', '7', '9', '1');
VENDOR  CUSTOMER
------------------
1   2
2   3
2   4
1   7
7   8
1   9
9   10
9   12
1   1 --This one is loop and is not taken to final resultset

一旦我们指定了NOCYCLE，检测到的循环将被丢弃，并标记导致循环记录的前一行为CONNECT_BY_ISCYCLE = 1。
第三步：
select * from t where vendor in ('2', '3', '4', '7', '8', '9', '10', '12');
VENDOR  CUSTOMER
------------------
2   3
2   4
4   5
4   6
7   8
9   10
10  11
9   12
4   4 --This one is loop

直到输出中至少有一条记录，才会继续这样做。虽然需要花费时间和耐心，但查询返回的结果是完全可重现和合法的，我感觉非常准确。这是Oracle算法的工作方式，因此每个人在编写查询时都必须记住这一点。
我们如何避免在顶级节点上进行循环？我建议添加虚拟记录，使我们的顶级节点不再是顶级节点。请考虑以下内容：
insert into t values(null, '1');

select vendor, 
       customer, 
       level, 
       connect_by_isleaf as isleaf, 
       connect_by_iscycle as iscycle, 
       connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
from t
connect by nocycle
      vendor=prior customer
start with vendor is null; --Note the changed condition

Vendor Customer Level   Isleaf  Iscycle  Path
------------------------------------------------------------
        1       1       0       1        ~ 1
1       2       2       0       0        ~ 1 ~ 2
2       3       3       1       0        ~ 1 ~ 2 ~ 3
2       4       3       0       1        ~ 1 ~ 2 ~ 4
4       5       4       1       0        ~ 1 ~ 2 ~ 4 ~ 5
4       6       4       0       1        ~ 1 ~ 2 ~ 4 ~ 6
6       9       5       0       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9
9       10      6       0       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11      7       1       1        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12      6       1       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1       7       2       0       0        ~ 1 ~ 7
7       8       3       1       0        ~ 1 ~ 7 ~ 8
1       9       2       0       0        ~ 1 ~ 9
9       10      3       0       0        ~ 1 ~ 9 ~ 10
10      11      4       0       0        ~ 1 ~ 9 ~ 10 ~ 11
11      2       5       0       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2
2       3       6       1       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2       4       6       0       1        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4       5       7       1       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4       6       7       1       1        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12      3       1       0        ~ 1 ~ 9 ~ 12

当然，在生产数据库中添加新记录可能不合适。相反，将实际表格的查询与某些动态确定顶级节点的查询结合起来。类似于这样（提供与上文相同的输出）：
delete from t where vendor is null; --Removing previosly inserted record

select vendor, 
       customer, 
       level, 
       connect_by_isleaf as isleaf, 
       connect_by_iscycle as iscycle, 
       connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
from (select vendor, customer from t
      union all
      select distinct null, vendor from t
      where vendor = 1) --Here is your START WITH condition
connect by nocycle
      vendor=prior customer
start with vendor is null;
-1
从一个节点开始并连接到另一个节点并不是同一件事情。ISCYCLE寻找客户~供应商的连接，并且每次它经过一条路径时只连接一次。如果您告诉Oracle：

START WITH vendor = '1'

它实际上会同时从4个点开始：

1 ~ 1
1 ~ 2
1 ~ 7
1 ~ 9

这些路径搜索是并行执行的，每个路径都尝试避免与其自身的路径循环。每个路径不知道其他路径的任何信息。因此，以1〜1开头的路径不知道为什么应该停止继续到2、7和9，因为它以前没有到过那里。NOCYCLE只是禁止再次查看1。因此，您可以使用START WITH（vendor ='1'AND customer！='1'）来避免太多的起点和/或忽略任何供应商和客户相同的连接：
CONNECT BY NOCYCLE ( vendor = PRIOR customer AND vendor != customer )
客户！=1将删除1上的循环标志，仅此而已。CONNECT BY NOCYCLE（vendor = PRIOR customer AND vendor！= customer）也不会产生所需的结果。 - Srini V
@Swen Vermeul这篇文章提供了深入的见解，强调了CONNECT BY子句和START WITH子句是独立操作的。CONNECT BY会独立限制连接的记录，而不管START WITH选择的初始记录。START WITH子句中的'customer !='1''可以防止整个树结构的重复; 然而，由于1 ~ 1不存在，因此没有标记为循环，我想知道循环的情况。在CONNECT BY子句中的vendor != customer也使4 ~ 4的循环不被标记。 - Larry May
我喜欢在START WITH子句中使用vendor != customer，以及在CONNECT BY子句中使用customer!= 1。实际上，我更喜欢使用customer != CONNECT_BY_ROOT vendor，但这不被支持，这会防止所有通过根节点的循环，但它也无法将其标记为循环。我想知道这些循环是否存在。 - Larry May
-1

nocycle 实际上允许您的查询具有循环，如果没有该关键字，Oracle 将在检测到循环时停止 (ORA-01436: CONNECT BY loop in user data)。它还允许您使用 "CONNECT_BY_ISCYCLE" 来检测孩子节点形成循环的位置，但是在该结果上过滤查询将删除有效行。 因此，也许您可以在循环条件中使用 connect by nocycle vendor=prior customer AND connect_by_iscycle = 0，以避免在检测到循环的父级之后出现所有循环？（我没有测试的东西）。这将在第一个 1~1 路径上停止递归。

CONNECT_BY_ISCYCLE=0 不会产生期望的结果，而是部分结果。 - Srini V
@realspirituals。这并不应该被评为-1。我认为这会消除全部树的复制。 - regilero
抱歉之前有些失礼，我在寻找一个可信的答案而不是未经测试的代码片段。因此我给了这个回答一个负分。当你测试它时，你会看到结果。（我确实看到了你的评论说它没有被测试过） - Srini V
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接