为什么Oracle与nocycle连接遵循root循环-Java 学习之路

有没有人知道为什么当循环发生在顶级节点（根节点连接到根节点）时，Oracle继续遵循循环循环之外的路径？更重要的是，如何预防呢？

我有Oracle 11g第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查询遍历树没有问题：

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'  );

还有一个供应商，其中客户是其供应商的供应商......

--ancestor cycle
    insert into t values ( '6'  , '2'  );

重新执行上面的选择查询导致与上面相同的输出，除了第3行和第5行（路径1~2~4和1~2~4~6）的Iscycle为1 . 请注意，CONNECT BY命名法标记循环的父记录而不是实际完成循环的子记录 . （所以我知道4和6都循环回祖先，但我不知道哪个祖先 . ）

再添加两条记录会在原始树的分支上创建一个更大的循环：

--cycle crossing branches of tree
  insert into t values ( '6'  , '9'  ); 
  insert into t values ( '11' , '2'  );

再次执行select查询会提供以下输出：

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

输出继续如预期 . 所有循环都是flaged，并且在遇到循环时映射停止 .

现在问题是孩子......让我们为根节点添加一个自循环，这与上面用节点4创建的第一个循环完全相同;仅适用于节点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';

具有讽刺意味的是，所有这一切都是从第一行中删除循环标志 .

任何帮助，将不胜感激 .

4 回答

Oracle选择层次结构的 root row(s) （满足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的子节点，因为CONNECT_BY_ROOT子句将LEVEL设置为循环

输出中的重复也发生在 connect by works on root which is duplicated 之后 .

Oracle is not able to restrict the uniqueness since Oracle can't give preference to one of the other

要么使您的数据集唯一，要么对它们进行编码，以便oracle可以在层次结构中的首选项上工作

FOLLOW UP: SOLUTION FOR OP's problem

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

回复于 2024-04-26T12:54:49+08:00

我同意@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的algorythm工作方式，所以每个人在编写查询时都必须牢记这一点 .

我们如何避免在顶级节点上循环？我建议添加虚拟记录，使我们的顶级节点不是最重要的节点 . 考虑一下：

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;

回复于 2024-04-26T12:54:49+08:00

-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 )
```
回复于 2024-04-26T12:54:49+08:00
-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路径上停止递归 .

回复于 2024-04-26T12:54:49+08:00

为什么Oracle与nocycle连接遵循root循环

4 回答

相关问题