부모가 소유한 루트의 백분율 계산

부모가 소유한 루트의 백분율 계산

간단히 말하면, 저는 부모가 소유한 나무의 뿌리, 더 위에 있는 나무의 비율을 계산하려고 합니다.SQL 단독으로 어떻게 해야 합니까?

여기 제 (샘플) 스키마가 있습니다.계층 구조 자체는 매우 간단하지만, 추가적으로holding_id, 즉, 한 부모가 자녀의 여러 부분을 "소유"할 수 있다는 뜻입니다.

create table hierarchy_test ( 
       id number -- "root" ID
     , parent_id number -- Parent of ID
     , holding_id number -- The ID can be split into multiple parts
     , percent_owned number (3, 2)
     , primary key (id, parent_id, holding_id) 
        );

그리고 몇 가지 샘플 데이터:

insert all 
 into hierarchy_test values (1, 2, 1, 1) 
 into hierarchy_test values (2, 3, 1, 0.25)
 into hierarchy_test values (2, 4, 1, 0.25)
 into hierarchy_test values (2, 5, 1, 0.1)
 into hierarchy_test values (2, 4, 2, 0.4)
 into hierarchy_test values (4, 5, 1, 1)
 into hierarchy_test values (5, 6, 1, 0.3)
 into hierarchy_test values (5, 7, 1, 0.2)
 into hierarchy_test values (5, 8, 1, 0.5)
select * from dual;

SQL Fiddle

다음 쿼리는 제가 하고 싶은 계산을 반환합니다.SYS_CONNECT_의 특성상BY_PATH는 계산 자체를 수행할 수 없는 것으로 알고 있습니다.

 select a.*, level as lvl
      , '1' || sys_connect_by_path(percent_owned, ' * ') as calc
   from hierarchy_test a
  start with id = 1
connect by nocycle prior parent_id = id

데이터에는 순환 관계가 있지만 이 예제에서는 그렇지 않습니다.

지금은 아주 간단한 기능을 사용해서 줄을 돌려야겠어요.calc숫자에 열을 올리다

create or replace function some_sum ( P_Sum in varchar2 ) return number is
   l_result number;
begin  
   execute immediate 'select ' || P_Sum || ' from dual'
     into l_result;
     
   return l_result;   
end;
/

이것은 말도 안 되는 방법인 것 같고, 동적¹ SQL을 구문 분석하는 데 걸리는 추가 시간을 피하고 싶습니다.

이론적으로는 MODEL 조항을 이용해서 이를 계산할 수 있어야 한다고 생각합니다.저의 문제는 나무의 특이함 때문에 발생합니다.이를 위해 MODEL 절을 사용하려는 시도 중 하나는 다음과 같습니다.

select *
  from ( select a.*, level as lvl
              , '1' || sys_connect_by_path(percent_owned, ' * ') as calc
           from hierarchy_test a
          start with id = 1
        connect by nocycle prior parent_id = id
                 )
 model
 dimension by (lvl ll, id ii)
 measures (percent_owned, parent_id )
 rules upsert all ( 
   percent_owned[any, any]
   order by ll, ii  = percent_owned[cv(ll), cv(ii)] * nvl( percent_owned[cv(ll) - 1, parent_id[cv(ll), cv(ii)]], 1)
               )

이는 다음과 같은 경우에 실패하는 것은 당연합니다.

ORA-32638: 모델 차원에서 고유하지 않은 주소 지정

UNIQUE SINGLE REFERENCE를 사용하는 것도 비슷한 이유로 실패합니다. 즉, ORDER BY 절이 고유하지 않기 때문입니다.

tl;dr

SQL만 사용하여 부모가 소유한 나무의 뿌리 비율을 계산하는 간단한 방법이 있습니까?만약 제가 MODEL의 궤도에 들어섰다면 어디가 잘못되고 있는 것일까요?

_{1. 또한 PL/SQL 컨텍스트 스위치를 피하고 싶습니다.저는 이것이 아주 적은 시간이라는 것을 알고 있지만, 이것은 하루에 몇 분을 더 추가하지 않고서는 빠르게 할 수 있을 만큼 충분히 어려울 것입니다.}

11g이면 아마...

SELECT a.*, LEVEL AS lvl
      ,XMLQuery( substr( sys_connect_by_path( percent_owned, '*' ), 2 ) RETURNING CONTENT).getnumberval() AS calc
   FROM hierarchy_test a
  START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;

SQL Fiddle.

아니면, 당신의 말대로'1'||속임수를 쓰는

SELECT a.*, LEVEL AS lvl
      , XMLQuery( ('1'|| sys_connect_by_path( percent_owned, '*' )) RETURNING CONTENT).getnumberval() AS calc
   FROM hierarchy_test a
  START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;

도 10g 에.XMLQuery함수를 허용할 수 없으며 평가를 위해 항상 문자열 리터럴을 기대합니다. 예를 들어-

select XMLQuery('1*0.25' RETURNING CONTENT).getnumberval() as val 
  from dual;

저작물과 반환0.25,그렇지만

select XMLQuery(substr('*1*0.25',2) RETURNING CONTENT).getnumberval() as val
   from dual;

주는ORA-19102: XQuery string literal expected.

트리의 레벨 수가 증가함에 따라 쿼리 속도가 느려질 수 있으며 내부 트리 생성에 따른 오버헤드가 추가됩니다.XMLQuery그 자체. 를 얻기 위한 가장최적의 방법은 여전히 PL/SQL 함수일 것이며, 이 함수는 10g과 11g 모두에서 작동할 것입니다.결과를 달성하기 위한 가장 최적의 방법은 여전히 PL/SQL 함수이며, 이 함수는 10g과 11g 모두에서 작동합니다.

이것은 답을 얻을 만한 가치가 있습니다. 비록 우리는 몇 가지 특별한 상황에서 운영되고 있지만 말입니다.

가장 먼저 언급해야 할 것은 Daniel Hilgarth 및 jonearls가 언급한 바와 같이 재귀적 하위 쿼리 팩토링/재귀적 CTE를 사용하는 것입니다.

with temp (id, parent_id, percent_owned, calc) as (
  select a.id, a.parent_id, a.percent_owned, percent_owned as calc
    from hierarchy_test a
   where id = 1
   union all
  select a.id, a.parent_id, a.percent_owned, a.percent_owned * t.calc as calc
    from temp t
    join hierarchy_test a
      on t.parent_id = a.id
         )
select * 
  from temp

그들의 SQL Fiddle..

불행히도 쿼리의 복잡성과 #작업 중인 데이터의 크기가 너무 복잡하여 불가능한 것으로 드러났습니다.매번 너무 큰 테이블을 완전히 스캔하지 않고는 할 수 없었습니다.

이것은 우리가 다시 그들의 집으로 돌아갔다는 것을 의미하지는 않습니다.CONNECT BY할 수 계층을 대량으로 계산할 수 있는 기회가 있습니다.불행히도 이것도 불가능한 것으로 밝혀졌습니다. 데이터베이스에서 한 시간 동안 충돌이 발생했습니다.세 번.Undo를 거의 100GB나 사용하고 있었는데 서버가 대처할 수가 없었습니다.우리는 UNDO를 거의 100GB나 사용하고 있었는데 서버가 대처할 수 없었습니다.

이것들이 특별한 상황입니다. 우리는 기껏해야 몇 시간 안에 수십만 개의 계층을 계산해야 합니다.평균적인 것은 약 1.5단계 깊이에 약 5-10개의 잎과 총 8-12개의 마디가 있습니다.그러나 특이치는 90k개의 노드, 27개의 수준 및 여러 개의 순환 관계를 갖습니다.이상점들은 어디에도 충분히 드물지 않습니다.

그렇게,CONNECT BY. PL/SQL에 대한 Annjawn의 솔루션 벤치마킹EXECUTE IMMEDIATE질문에 제시된 것은 평균 이상의 나무에 대해 다음과 같습니다.XMLQuery()4배까지 느려졌습니다.좋아요, 답이 있었어요. 다른 선택의 여지는 없어요. 그냥 두십시오.

것은 아니다.

수많은 노드를 가진 수많은 계층을 계산하고 있기 때문에 수십만 개의 수학 함수를 계속 하드 파싱하여 발생하는 라이브러리 캐시 핀 잠금 상태에서 대기 시간이 지나치게 길어졌습니다.EXECUTE IMMEDIATE.

이에 대한 명확한 응답이 없으므로 Annjawn의 솔루션으로 돌아가면 3배 더 빨리 끝납니다!도서관 현금 핀 잠금 장치가 완전히 사라지고 우리는 다시 곧고 좁은 길로 접어들었습니다.

것은 아니다.

안타깝게도 11.2에 오라클 버그가 있는 것 같습니다.CONNECT BY,XMLQuery()DBMS_SCHEDULEER.어떤 상황에서는, 보통 큰 계층에서는, 엄청난 양의 메모리가 유출됩니다.데이터베이스와 서버가 그 중 하나를 찾아냈습니다.Oracle에서 보고서가 제출되어 12c에서 테스트를 진행하고 있습니다. 메모리 유출이 적지만 여전히 12c가 나오지 않는 것으로 보입니다.

해결책?포장.XMLQuery()PL/SQL 함수에서.메모리 누수가 해결되었습니다. 불행히도 이로 인해 이 기능에 대한 많은 경합이 발생하여 여러 시간 동안 라이브러리 캐시: mutex x waits..조회 중x$kglob확인했습니다.XMLTYPE망치질을 하고 있었습니다.

Andrey Nikolaev는 시스템을 변경할 것을 권장합니다. 다른 모든 것이 정상적으로 작동할 때는 변경하지 말 것을 권장하거나, Oracle에게 이 개체에 많이 액세스할 것이라고 알리는 절차를 사용하지 말 것을(를) 권장합니다.일반적으로 봤을 때 문제는 약 10분 후에 해결되었을 수도 있지만 Oracle이 가지고 있는 모든 잠금 상태로 보이는 작업을 수행한 결과 CPU를 놓고 경쟁하는 5개의 프로세스가 발생했습니다. (테스트 박스에 54GB 및 24개 코어)가 충분하지 않았던 것 같습니다.

그런 다음 커서 핀: 대기를 받기 시작했습니다.Bulleson은 더 많은 숨겨진 매개 변수 핀앵글링을 추천하고 Jonathan Lewis는 SGA 크기 조정을 제안합니다.DB가 자동화된 SGA 사이징을 사용하고 있었기 때문에 우리는 공유 풀을 최대 30GB까지 점진적으로 늘리려고 시도했지만 라이브러리 캐시인 mutex x wait만 되찾았습니다.

그렇다면 해결책은?누가 알겠는가, 하지만 자바 저장 프로시저는 지금까지 메모리 유출, 대기 시간 및 다른 모든 것보다 훨씬 빠른 속도로 훌륭하게 작동하고 있습니다.

더 많은 것이 있을 겁니다그리고 나는 정말로 그것을 얻고 싶습니다.MODEL아이디어가 있는 사람이 있으면 일하는 조항?

추신. 이 모든 것에 대해 공을 인정할 수는 없습니다. 우리를 이 단계에 이르게 하는 것은 3명 정도의 사람들의 일입니다.

언급URL : https://stackoverflow.com/questions/13806910/calculate-the-percentage-of-the-root-owned-by-its-parents