sql – 深度嵌套的子查询因子分解(CTE)的性能降低

此查询包含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分钟才能完成.

Q1.
为什么相同数据的相同子查询的计算时间差别很大？

Q2.
它看起来像一个bug,因为它在Oracle 9上运行速度非常快,在Oracle 11上运行速度非常慢.
实际上,每个带有long和complex with子句的select语句都会以相同的方式运行.
这是一个已知的bug吗？ (我没有访问metalink)
建议使用哪种解决方法？

Q3.
我必须为Oracle 11编写代码,我必须在单个select语句中完成所有计算.
我不能在两个单独的陈述中将我的长篇陈述分开以加速它.
在Oracle(或者某些技巧)中是否存在提示使整个查询(t16)在合理的时间内完成(例如,在一秒内)？我试图找到这样的但无济于事.
顺便说一句,执行计划非常好,而且成本表现为步数的线性函数(非指数).

解决方法

Q2：Oracle 11.X.0.X中存在许多已知和修复的错误,这些错误与嵌套查询和查询因子分解的优化有关.但很难找到具体问题.

问题3：有两个未记录的提示：实现和内联但在我尝试你的例子时,没有一个适合我.服务器配置或升级到11.2.0.3的某些更改可能会增加嵌套子句的限制：对于我(在11.2.0.3 Win7 / x86上),您的示例工作正常,但嵌套表的数量增加到30会挂起一个会话.

解决方法可能如下所示：

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.

更新

>刚刚安装了11.2.0.4(Win7 / 32bit)并根据初始查询进行测试.优化器行为没有任何改变.
>即使使用内联(未记录)或RULE(已弃用)提示,也不可能直接影响CBO行为.可能是一些Guru知道一些变体,但它对我来说是个绝密(也是谷歌:-).
>如果主select语句被分成一个部分并放入返回一组行的函数(函数返回sys_refcursor或强类型游标),那么在合理的时间内在一个select语句中做事是可能的,但是如果a不是一个选择在运行时构造的查询.
>使用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",extractvalue(column_value,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",level),xmlelement("k2",level + 3))) t0
          from dual connect by level < 5
        )
      )
    )
  )))

关于上面的奇怪代码的另一个问题是,此变体仅适用于数据集没有大量行的情况.

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!