编码人员水平直接决定系统性能

原贴http://www.itpub.net/viewthread.php?tid=900313&extra=&page=1

此SQL如何优化?

SELECT MAX(A.YYYYMM)
                                                   FROM TM_AVG_ICE A,Tm_Avg_ice B
                                                  WHERE A.MODEL    = B.MODEL
                                                    AND A.SUFFIX   = B.SUFFIX
                                                    AND A.COM_CODE = 'LH'

==>

SELECT MAX(A.YYYYMM)
FROM TM_AVG_ICE A
where
A.MODEL    is not null
AND A.SUFFIX  is not null
and  A.COM_CODE = 'LH'

乍一看似乎不可思议，可实际上就是这样：

==>
SELECT MAX(A.YYYYMM)
                                                   FROM TM_AVG_ICE A
                                                  WHERE  A.COM_CODE = 'LH'
                                                  and exists (select 1 from TM_AVG_ICE B
                                                                   where A.MODEL    = B.MODEL
                                                    AND A.SUFFIX   = B.SUFFIX )  ;

==>

SELECT MAX(A.YYYYMM)
FROM TM_AVG_ICE A
where
A.MODEL    is not null
AND A.SUFFIX  is not null
and  A.COM_CODE = 'LH'

这种情况屡有出现，业务抽象不够理想，开发人员业务逻辑不清，出现这样的情况很正常，如果不跳出sql本身去分析，又是很难有突破

如：

SELECT so_outbalance_b.coutid,
sum(nvl(so_outbalance_b.norigoutbalmny, 0) +
           nvl(so_outbalance _b.norigretmny, 0) +
           nvl(so_outbalance_b.narsubinvmny, 0))
FROM so_outbalance_b
WHERE exists (SELECT ic_general_h.cgeneralhid
FROM ic_general_h, ic_general_b, so_saleorder_b
WHERE ic_general_
         h.cgeneralhid = ic_general_b.cgeneralhid
and ic_general_b.cfirstbillhid = so_saleorder_b.csaleid a nd
         ic_general_b.cfirstbillbid = so_saleorder_b.corder_bid
and (dbilldate = '2007-11-20')
and ic_ general_h.pk_corp = '1019')
GROUP BY so_outbalance_b.coutid

 

结合业务很容易发现，语句缺乏强关联

 

from so_outbalance_b,ic_general_h, ic_general_b, so_saleorder_b
where   ic_general_h.cgeneralhid = ic_general_b.cgeneralhid
           and ic_general_b.cfirstbillhid = so_saleorder_b.csaleid
           and ic_general_b.cfirstbillbid = so_saleorder_b.corder_bid
           and (dbilldate = '2007-11-20')
           and ic_ general_h.pk_corp = '1019')
           GROUP BY so_outbalance_b.coutid

这样一看是不是就很清楚了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27378/viewspace-629878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27378/viewspace-629878/