计算由其父母拥有的根目录的百分比。

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 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：在MODEL维度中存在非唯一性寻址

使用UNIQUE SINGLE REFERENCE也会因相似原因失败，即ORDER BY子句不唯一。

tl;dr

是否有一种简单的方法只使用SQL计算树根由其父级拥有的百分比？如果我通过MODEL走在正确的轨道上，那么我错在哪里了？

_{1.我还想避免PL/SQL SQL上下文切换。我意识到这只是一个很小的时间量，但是要在几分钟内快速完成这项任务已经很困难了，再增加几分钟会更加困难。}

- Ben

---

你是否考虑过使用递归子查询因子而不是connect-by？ - Jon Heller

@jonearles：我也想问同样的问题 :-) 我实际上尝试使用那种方法来解决问题，但它没有成功：http://www.sqlfiddle.com/#!4/c3d5d/15 - Daniel Hilgarth

当然，@gloomy.penguin，这是您要的：http://i.stack.imgur.com/r9Ziu.png。它不是非常复杂。 - Ben

这篇文章提供了一些关于在Oracle中对分层表进行聚合的建议：http://stackoverflow.com/questions/12221047/oracle-sql-hierarchical-query-flatten-hierarchy-and-perform-aggregation，其中还有一些链接指向其他你可能想要查看的SO问题。我尝试了一下，但没有什么好的结果... - gloomy.penguin

@DanielHilgarth 我认为你差不多就做到了。你只需要将t.id = a.parent_id改为t.parent_ID = a.id即可。 - Jon Heller

Answer 1

在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字符串字面值期望的错误。

随着树的层数增加，查询可能会变得更慢，并且由于XMLQuery自身的内部树创建而产生额外开销。最优的方法仍然是使用PL/SQL函数来实现结果，顺便说一句，这种方法适用于10g和11g。

- Anjan Biswas

---

非常感谢，这绝对很棒，我一定会记住它以备将来之需。不幸的是，与使用 PL/SQL 相比，它也慢得多。我在一个大树上进行了基准测试，速度慢了 4 倍。 - Ben

是的，它不是用于评估表达式的目的，而是完成工作。由于它需要在每个connect by结果上创建一棵树，所以速度较慢，因此层级越多，速度可能会变慢。我也觉得PLSQL是更好的选择，但既然你正在寻找一个更简单的SQL方法来完成它，我只能想到这个。 :) - Anjan Biswas

事实证明你是对的，我们现在确实需要使用它。但是...我们遇到了一些小问题。 - Ben

Answer 2

这个问题值得回答；但需要注意我们在一些特殊情况下操作。

首先要提到的是，按照Daniel Hilgarth和jonearles在评论中提到的，最好的解决方法是使用递归子查询实现/使用递归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。有机会批量计算层次结构。不幸的是，这也是不可能的；数据库一个小时崩溃了三次。我们使用了将近100GB的UNDO，服务器无法承受。

这些是特殊情况；我们必须在几个小时内计算数十万个层次结构，最多不超过几个小时。平均每个层次结构大约有1.5个级别，可能有5-10个叶子和8-12个节点。然而，离群值具有90k个节点，27个级别和多个循环关系。离群值并不罕见。

所以，CONNECT BY。将Annjawn的解决方案与问题中建议的PL/SQL EXECUTE IMMEDIATE进行基准测试表明，在上平均树的情况下，XMLQuery()最多比后者慢4倍。很好，找到了答案；没有其他选择；就这样吧。

不是。

由于我们计算了如此多的具有如此多节点的层次结构，因此由于在EXECUTE IMMEDIATE中数十万个数学函数的不断硬解析而导致的library cache pin locks引起了过长的等待时间。

对此没有明显的反应，因此回到Annjawn的解决方案，它最多可以快3倍！ library cache pin locks完全消失了，我们回到了正轨。

不是。

不幸的是，在11.2中似乎存在一个Oracle错误，当您将CONNECT BY、XMLQuery()和DBMS_SCHEDULER组合在一起时，会在某些情况下（通常是在较大的层次结构中）泄漏大量内存。为了找出原因，我们失去了数据库和服务器。已向Oracle报告，并正在12c中进行测试；虽然内存泄漏表现得更少，但它们仍然会出现，因此12c被排除了。

解决方案？将XMLQuery()包装在PL/SQL函数中。内存泄漏问题得到解决，不幸的是，这导致此函数存在大量争用，并且我们开始出现多小时的Library cache: mutex x等待。查询x$kglob确认XMLTYPE正在被攻击。

Andrey Nikolaev建议要么修改系统；但是，当一切正常时最好不要这样做，要么使用DBMS_POOL.MARKHOT过程告诉Oracle你将经常访问此对象。对于普通用户来说，这可能已经解决了问题，然而，大约10分钟后，我们发现有5个进程争夺CPU，似乎涉及到Oracle的每一个锁。显然，这里缺少了足够的资源（测试框架上有54GB和24个核心）...

然后我们开始出现Cursor pin: s等待。 Burleson建议更改隐藏参数，Jonathan Lewis指出这是由于SGA调整大小而引起的。由于数据库正在使用自动SGA大小调整，我们尝试逐渐增加共享池，最多达到30GB，但只得到了老朋友Library cache: mutex x等待。

那么，解决方案是什么？诚实地说，谁知道呢，但Java存储过程目前工作得非常好，没有内存泄漏，没有等待时间，而且比其他方法快得多。

我相信还有更多的方法...如果有人有任何想法，我真的很想让MODEL子句起作用？

P.S. 我不能为所有这些内容声称功劳；这是大约3个人的工作把我们带到了这个阶段...

- Ben