sql – 对非分层数据的Oracle分层查询

CREATE TABLE T (parent INTEGER,child INTEGER)
               AS select 1 parent,2 child from dual
        union all select 1 parent,8 child from dual
        union all select 2 parent,3 child from dual
        union all select 2 parent,4 child from dual
        union all select 2 parent,8 child from dual
        union all select 3 parent,4 child from dual
        union all select 3 parent,6 child from dual
        union all select 4 parent,5 child from dual
        union all select 5 parent,8 child from dual
        union all select 6 parent,5 child from dual
        union all select 7 parent,3 child from dual
        union all select 7 parent,5 child from dual
        union all select 8 parent,6 child from dual

我的目标是获得节点X的所有后代(孩子,孩子的孩子等)的节点.假设2.我的预期结果是：3,4,5,6,8.

我知道我可以设计一个这样的查询：

SELECT child,sys_connect_by_path(child,'/')
   FROM T
  START WITH parent = 2
CONNECT BY NOCYCLE PRIOR child = PARENT;

这样一个查询的问题是,它会遍历所有可能的路径,直到它们循环,并且在我的实际数据中有太多的路径.结果包括许多重复 – 这里是：

child | sys_connect_by_path (for information)
3     | /3
4     | /3/4
5     | /3/4/5
8     | /3/4/5/8
6     | /3/4/5/8/6
6     | /3/6
5     | /3/6/5
8     | /3/6/5/8
4     | /4
5     | /4/5
8     | /4/5/8
6     | /4/5/8/6
8     | /8
6     | /8/6
5     | /8/6/5

我的实际数据要复杂得多.这样的查询的执行成本是如此巨大,使得我的TEMP表空间(可自动扩展)达到10Gb(最初为500Mb),并且由于磁盘已满,我的数据库实际上被破坏.

我试图设计这样的查询(递归WITH子句)：

WITH descendants(node) AS
( SELECT 2 node FROM dual
  UNION ALL
  (
  SELECT child
    FROM T
   INNER JOIN descendants D
      ON T.parent = D.node
   MINUS SELECT node FROM descendants
  )
)
SELECT * FROM descendants

我遇到的问题是：

>使用Oracle 10g,这没有实现(ORA-32033：不支持的列别名,一些客户使用Oracle 9或10)
>使用Oracle 11g,我得到ORA-32041：递归WITH子句中的UNION ALL操作必须只有两个分支.如果我删除了MINUS子句,我将获得循环(ORA-32044：在执行递归WITH查询时检测到循环).

您如何查询我的原始数据以有效地获得这些节点3,8？ PL / SQL解决方案也受到欢迎.

谢谢.

解决方法

如果它相对较小,您可以循环下来,同时检查您已访问的节点,以这样的方式…

(注意,我不是Oracle专家,所以这更接近于伪代码与一些真正的SQL混合在一起)

CREATE TABLE myMap (parent INT,child INT);

INSERT INTO myTable SELECT NULL,2 FROM DUAL;

WHILE (SQL%ROWCOUNT > 0)
LOOP

  INSERT INTO
    myMap
  SELECT DISTINCT
    dataMap.parent,dataMap.child
  FROM
    myMap
  INNER JOIN
    dataMap
      ON myMap.child = dataMap.parent
  WHERE
    NOT EXISTS (SELECT * FROM myMap WHERE parent = dataMap.parent)

END LOOP;

根据性能,您可能还希望在myMap中有一个深度字段;优化连接,以便只加入最近的节点.这意味着两个指标;一个用于JOIN(深度),一个用于NOT EXISTS(父).

编辑

添加了DISTINCT关键字,以避免以下情况…
– 节点2映射到3和4
– 节点3和4都映射到节点5
– 节点5的所有子节点现在将被处理两次

GROUP BY或许多其他选项可以用来满足这一点,而不是DISTINCT.只是它自己的“不存在”是不够的.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!