oracle中select嵌套,最内层的select用到了最外层的select的from中的表导致速度很慢,改如何解决?

zhaozhen 发布于 2013/02/21 14:06
阅读 2K+
收藏 0

我有这样一条sql语句(oracle中select嵌套,最内层的select用到了最外层的select的from中的表导致速度很慢,改如何解决?)

select  (select count(estimated1_.ESTIMATED_ID)
                 from LBIS.LBIS_ESTIMATED estimated1_
                where estimated1_.CONSIGN_ID in
                      (
                      select estimated4_.CONSIGN_ID
                          from LBIS.LBIS_PAYMENT_ITEM   paymentite2_,
                               LBIS.LBIS_ESTIMATED_ITEM estimatedi3_,
                               LBIS.LBIS_ESTIMATED      estimated4_
                         where paymentite2_.PAYMENT_ITEM_ESTIMATED_ITEM =
                               estimatedi3_.EI_ID
                           and estimatedi3_.EI_ESTIMATED =
                               estimated4_.ESTIMATED_ID
                           and estimated4_.ESTIMATED_SERVICETYPE = '1'
                           and paymentite2_.PAYMENT_ITEM_PAYMENT =
                               payment0_.PAYMENT_ID
                      )) as col_2_0_
          from LBIS.LBIS_PAYMENT payment0_

在oracle运行速度很慢,经过测试发现语句最后的and paymentite2_.PAYMENT_ITEM_PAYMENT =
                               payment0_.PAYMENT_ID中的

payment0_表是最外层的表被最内层的select用到了,把payment0_.PAYMENT_ID改为具体数值,速度提高相当明显。请教各位高手该如何优化??
加载中
0
魔力猫
魔力猫
你是里面数据量大还是外面数据量大?又是in和exists的问题吧。
0
Y-QTCe
Y-QTCe
有点囧,这不是完整的一句吧,怎么会写得这么扭曲
0
z
zhaozhen

应该和in没多大关系吧,我把in改成了exists速度还是很慢。应该是最里层的select用到了最外层的字段,把最里层那个用到最外层字段改成具体的值,速度就很快了。

返回顶部
顶部