sql - 深度嵌套子查询分解 (CTE) 性能缓慢

Question

该查询由 16 个相等的步骤组成。
每一步都是对同一数据集(单行)进行相同的计算，
但最后的步骤需要太多时间。

with t0 as (select 0 as k from dual)
,t1 as (select k from t0 where k >= (select avg(k) from t0))
,t2 as (select k from t1 where k >= (select avg(k) from t1))
,t3 as (select k from t2 where k >= (select avg(k) from t2))
,t4 as (select k from t3 where k >= (select avg(k) from t3))
,t5 as (select k from t4 where k >= (select avg(k) from t4))
,t6 as (select k from t5 where k >= (select avg(k) from t5))
,t7 as (select k from t6 where k >= (select avg(k) from t6))
,t8 as (select k from t7 where k >= (select avg(k) from t7))
,t9 as (select k from t8 where k >= (select avg(k) from t8))
,t10 as (select k from t9 where k >= (select avg(k) from t9))
,t11 as (select k from t10 where k >= (select avg(k) from t10))
,t12 as (select k from t11 where k >= (select avg(k) from t11)) -- 0.5 sec
,t13 as (select k from t12 where k >= (select avg(k) from t12)) -- 1.3 sec
,t14 as (select k from t13 where k >= (select avg(k) from t13)) -- 4.5 sec
,t15 as (select k from t14 where k >= (select avg(k) from t14)) -- 30 sec
,t16 as (select k from t15 where k >= (select avg(k) from t15)) -- 4 min
select k from t16

子查询 t10 立即完成，但整个查询 (t16) 需要 4 分钟才能完成。

第一季度。
为什么相同数据的相同子查询的计算时间相差很大？

第二季度。
它看起来像是一个错误，因为它在 Oracle 9 上运行得非常快，但在 Oracle 11 上运行得非常慢。
事实上，每个带有长且复杂的 with 子句的 select 语句都会以相同的方式运行。
这是一个已知的错误吗？ (我无权访问metalink)
建议采用什么解决方法？

第三季度。
我必须为 Oracle 11 编写代码，并且必须在单个 select 语句中完成所有计算。
我无法将长语句分成两个单独的语句来加快速度。
Oracle 中是否存在提示(或者可能是某种技巧)来使整个查询(t16)在合理的时间内(例如，一秒内)完成？我试图找到这样的一个，但没有成功。
顺便说一句，执行计划非常好，并且成本表现为步骤数的线性函数(不是指数)。

Answer 1

Q1:似乎与计算时间无关，只是优化器算法中的错误使其在计算最佳执行计划时变得疯狂。

Q2:Oracle 11.X.0.X 中有许多与嵌套查询和查询分解优化相关的已知且已修复的错误。但很难找到具体的问题。

Q3:有两个未记录提示:materialize和inline但当我尝试你的例子时，它们都不适合我。服务器配置中的某些更改或升级到 11.2.0.3 可能会增加嵌套 with 的限制子句:对我来说(在 11.2.0.3 Win7/x86 上)，你的示例工作正常，但是将嵌套表的数量增加到 30 会挂起 session 。

解决方法可能如下所示:

select k from (
select k, avg(k) over (partition by null) k_avg from ( --t16
  select k, avg(k) over (partition by null) k_avg from ( --t15
    select k, avg(k) over (partition by null) k_avg from ( --t14
      select k, avg(k) over (partition by null) k_avg from ( --t13
        select k, avg(k) over (partition by null) k_avg from ( --t12
          select k, avg(k) over (partition by null) k_avg from ( --t11
            select k, avg(k) over (partition by null) k_avg from ( --t10
              select k, avg(k) over (partition by null) k_avg from ( --t9
                select k, avg(k) over (partition by null) k_avg from ( --t8
                  select k, avg(k) over (partition by null) k_avg from ( --t7
                    select k, avg(k) over (partition by null) k_avg from ( --t6
                      select k, avg(k) over (partition by null) k_avg from ( --t5
                        select k, avg(k) over (partition by null) k_avg from ( --t4
                          select k, avg(k) over (partition by null) k_avg from ( --t3
                            select k, avg(k) over (partition by null) k_avg from ( --t2
                              select k, avg(k) over (partition by null) k_avg from ( -- t1
                                select k, avg(k) over (partition by null) k_avg from (select 0 as k from dual) t0
                              ) where k >= k_avg
                            ) where k >= k_avg
                          ) where k >= k_avg
                        ) where k >= k_avg
                      ) where k >= k_avg
                    ) where k >= k_avg
                  ) where k >= k_avg
                ) where k >= k_avg
              ) where k >= k_avg
            ) where k >= k_avg
          ) where k >= k_avg
        ) where k >= k_avg
      ) where k >= k_avg
    ) where k >= k_avg
  ) where k >= k_avg
) where k >= k_avg
)

至少它在 30 级嵌套级别上对我有效，并产生与 WINDOW BUFFER 完全不同的执行计划和VIEW而不是LOAD TABLE AS SELECT , SORT AGGREGATE和TABLE ACCESS FULL .

更新

1. 刚刚安装了 11.2.0.4 (Win7/32bit) 并根据初始查询对其进行了测试。优化器行为没有任何变化。

2. 即使使用 inline 也不可能直接影响 CBO 行为。 (无证)或RULE (已弃用)提示。可能某些大师知道某些变体，但这对我(以及 Google 来说也是最高 secret :-)。

3. 如果将主 select 语句分成几个部分并放入返回一组行的函数(返回 sys_refcursor 或强类型游标的函数)中，则可以在合理的时间内在一个 select 语句中执行操作，但它是如果查询是在运行时构建的，则不是一个选择。

4. 使用 XML 的解决方法是可能的， 但是这个变体看起来像是通过屁眼去除扁桃体 (抱歉):

.

select
  extractvalue(column_value,'/t/somevalue') abc
from 
  table(xmlsequence((
    select t2 from (
      select
        t0,
        t1,
        (   
          select xmlagg(
                   xmlelement("t", 
                     xmlelement("k1",extractvalue(t1t.column_value,'/t/k1')), 
                     xmlelement("somevalue", systimestamp))
                  )
          from 
            table(xmlsequence(t0)) t0t, 
            table(xmlsequence(t1)) t1t  
          where 
            extractvalue(t1t.column_value,'/t/k1') >= (
              select avg(extractvalue(t1t.column_value, '/t/k1')) from table(xmlsequence(t1))
            )                                              
            and 
            extractvalue(t0t.column_value,'/t/k2') > 6
        ) t2
      from (
        select
          t0,
          (
            select xmlagg(
                     xmlelement("t", 
                       xmlelement("k1",extractvalue(column_value,'/t/k1')), 
                       xmlelement("somevalue", sysdate))
                    )
            from table(xmlsequence(t0))   
            where 
              extractvalue(column_value,'/t/k1') >= (
                select avg(extractvalue(column_value, '/t/k1')) from table(xmlsequence(t0))
              )
          ) t1
        from (
          select
            xmlagg(xmlelement("t", xmlelement("k1", level), xmlelement("k2", level + 3))) t0
          from dual connect by level < 5
        )
      )
    )
  )))

关于上面的奇怪代码的另一件事是，此变体仅适用于 with数据集的行数不多。