计算其父母拥有的根的百分比

Question

简而言之，我正在尝试计算其父本拥有的一棵树的根的百分比，并进一步计算该树的根。我该如何单独在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小提琴

以下查询返回我要进行的计算。由于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 1会花费额外的时间。

从理论上讲，我认为我应该能够使用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 SQL上下文切换。我意识到这是很少的时间，但是要每天不增加额外的几分钟就很难快速地做到这一点。

Answer 1

这应该得到答案；尽管请注意，我们在一些特殊情况下进行操作。

首先要提到的是，做到这一点的最佳方法是使用递归子查询分解/递归CTE，如评论中的Daniel Hilgarth和jonearles所述：

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小提琴。。

不幸的是，查询的复杂性和我们正在处理的数据量是如此之大，以至于事实证明这是不可能的。如果没有每次都完全扫描一些过大的表，就无法做到这一点。

这并不一定意味着我们会回到CONNECT BY。有机会批量计算层次结构。不幸的是，事实证明这也是不可能的。一个小时的数据库崩溃了。三次。我们用完了将近100GB的UNDO，而服务器却无法应付。

这些是特殊情况；我们最多只能在几个小时内计算成千上万的层次结构。平均一个深约1.5级，总共可能有5-10片叶子和8-12个节点。但是，离群值有90k节点，27个级别和多个html" target="_blank">循环关系。离群值离稀有度还远。

因此，CONNECTBY。问题中提出的针对Annjawn针对PL/ SQL的解决方案进行基准测试EXECUTEIMMEDIATE表明，对于高于平均水平的树XMLQuery()，速度要慢4倍。太好了，有答案了；没有其他选择；留在那。

不是。

由于我们要计算具有这么多节点的如此多的层次结构，因此最终由于 库中 的数十万个数学函数的不断硬解析而导致 库缓存引脚锁定的
等待时间过长EXECUTE IMMEDIATE。

对此没有明显的反应，所以回头再回想一下Annjawn的解决方案，它的速度提高了3倍！该 库缓存销锁 完全消失，我们在直道上背部和缩小。

不是。

不幸的是，似乎是在11.2的Oracle错误当你把出现CONNECT BY，XMLQuery()和DBMS_SCHEDULER。在某些情况下，通常在较大的层次结构中，它会泄漏大量内存。丢失了数据库 ，
服务器发现了那个。Oracle提出了一份报告，我们正在12c中进行测试；尽管内存泄漏的表现较少，但仍会出现，因此12c已耗尽。

解决方案？将XMLQuery()PL / SQL函数包装起来。内存泄漏解决了，可惜这造成了大量的争用这一功能，我们开始越来越多小时的
库高速缓存：互斥X 等待…查询x$kglob确认是XMLTYPE这是酩酊大醉。

安德烈·尼古拉耶夫（Andrey
Nikolaev）建议您更改系统；而是在其他一切都正常的情况下不要这样做，或者使用该DBMS_POOL.MARKHOT过程告诉Oracle您将大量访问此对象。临时来看，这可能已经解决了问题，但是，大约10分钟之后，经历了Oracle似乎拥有的每一个锁，我们最终获得了5个争用CPU的进程。显然还不够（测试盒上有54GB和24个内核）…

然后，我们开始获取 Cursor pin：s 等待。Burleson建议使用更多隐藏参数修饰，Jonathan
Lewis建议将其归因于SGA调整大小。当数据库使用自动SGA大小调整时，我们尝试逐渐将 共享池 增加到最大30GB，并且只回收了老朋友
库缓存：互斥锁x 等待。

那么，有什么解决方案呢？谁知道这是一个诚实的答案，但是到目前为止，Java存储过程运行良好，没有内存泄漏，没有等待，而且比其他所有方法都快得多。

我敢肯定还有更多…MODEL如果有人有任何想法，我真的很想让该子句起作用。

附言：我不能为所有这些要求功劳；这是大约3个人的工作，可以使我们进入此阶段…