每一步都在同一个数据集(单行)上进行相同的计算,
但最后的步骤需要花费太多时间.
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
)
)
)
)))
关于上面的奇怪代码的另一个问题是,此变体仅适用于数据集没有大量行的情况.
