비계층 데이터에 대한 오라클 계층 쿼리

비계층 데이터에 대한 오라클 계층 쿼리

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: unsupported column aliasing일부 고객은 Oracle 9 또는 10을 사용합니다.
• Oracle 11g을 사용하면 다음과 같은 이점을 얻을 수 있습니다.ORA-32041: UNION ALL operation in recursive WITH clause must have only two branchesMINUS 조항을 제거하면 사이클(ORA-32044: cycle detected while executing recursive WITH query).

노드 3, 4, 5, 6, 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;

성능에 따라 다음과 같은 기능이 필요할 수도 있습니다.depth에 출전하다.myMap가장 최근 노드에서만 조인하도록 조인을 최적화합니다.이는 두 개의 인덱스, 즉 JOIN에 대한 인덱스를 의미합니다.(depth)그리고 존재하지 않는 것을 위한 하나.(parent).

편집

다음과 같은 경우를 피하기 위해 DISTINCT 키워드를 추가했습니다.
노드 2는 3과 4에 매핑됩니다.
노드 3과 4는 모두 노드 5에 매핑됩니다.
이제 노드 5의 모든 자식이 두 번 처리됩니다.

DISTINCT 대신 GROUP BY 또는 다른 많은 옵션을 사용하여 이를 충족할 수 있습니다.그것만으로는 존재하지 않는다는 것이 충분하지 않다는 것입니다.

제가 직접 작업한 것은 아니지만, NOCY 옵션이 포함된 CONNECT BY는 어떻습니까?그것은 나무가 고리를 볼 때 나무를 건너는 것을 멈출 것입니다.오라클 11i는 확실히 그것을 가지고 있습니다, 저는 그것이 오라클 10g 기간 어딘가에 들어온 것이라고 생각합니다.

방문한 데이터가 4000바이트를 초과할 때까지 도움이 될 수 있습니다.사이클이 가능해서는 안 되지만 선이 예시로 있습니다.

   WITH descendants(node, lvl, pth, visited) AS
    (
    SELECT child node, 1, cast(child as varchar2(4000)), '/'||listagg(child,'/') within group (order by child) over()||'/'
      FROM t 
     where parent = 2
     UNION ALL
    SELECT child, lvl+1, pth||'/'||child, D.visited||listagg(child,'/') within group (order by child) over()||'/'
      FROM T
     INNER JOIN descendants D
        ON T.parent = D.node
     WHERE D.visited not like '%/'||child||'/%'
    )
    cycle node set cyc to '1' default '0' 
    SELECT distinct node
      FROM descendants
     order by node
    ;

언급URL : https://stackoverflow.com/questions/7644791/oracle-hierarchical-query-on-non-hierarchical-data