Иерархический запрос Oracle к неиерархическим данным

У меня есть данные в таблице 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

Data sample

Моя цель - получить все узлы, являющиеся потомками (дочерние элементы, дочерние элементы дочерних узлов и т. Д.) Узла 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, которое было автоматически расширяемым, достигло 10 ГБ (первоначально 500 МБ), а моя база данных фактически сломалась из-за переполнения диска.

Я попытался создать такой запрос (рекурсивное предложение 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: операция UNION ALL в рекурсивном предложении WITH должна иметь только две ветви . Если я удалю предложение MINUS, я получу циклы ( ORA-32044: цикл обнаружен при выполнении рекурсивного запроса WITH ).

Как бы вы запросили мои исходные данные, чтобы эффективно получить эти узлы 3, 4, 5, 6, 8? Также приветствуются решения PL / SQL.

Спасибо.

14
задан Benoit 3 January 2012 в 10:06
поделиться