这应该得到答案;尽管请注意,我们在一些特殊情况下进行操作。
首先要提到的是,做到这一点的最佳方法是使用递归子查询分解/递归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
不幸的是,查询的复杂性和我们正在处理的数据量是如此之大,以至于事实证明这是不可能的。如果没有每次都完全扫描一些过大的表,就无法做到这一点。
这并不一定意味着我们会回到CONNECT BY
。有机会批量计算层次结构。不幸的是,事实证明这也是不可能的。一个小时的数据库崩溃了。三次。我们用完了将近100GB的UNDO,而服务器却无法应付。
这些是特殊情况;我们最多只能在几个小时内计算成千上万的层次结构。平均一个深约1.5级,总共可能有5-10片叶子和8-12个节点。但是,离群值有90k节点,27个级别和多个循环关系。离群值离稀有度还远。
因此,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个人的工作,可以使我们进入此阶段…