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
        )
      )
    )
  )))

关于上面的稀疏代码的另一个题目是,此变体仅合用于数据集没有大量行的环境.

（编辑：湖南网）

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