求SQL优化！

Question

      SELECT a.contno,
             a.polno,
             a.riskcode,
             a.managecom,
             a.salechnl,
             1 pol_state,
             a.amnt standamnt,
             a.prem,
             (select NVL(sum(amnt), 0) 
                from lcduty
               where polno = a.polno
                 AND a.bonusgetmode = '4'
                 and length(dutycode) != 6
                 and Substr(dutycode, 7, 1) = '1') bonusamnt,--
             (select NVL(sum(prem), 0) 
                from lcprem
               where payplantype in ('01', '03')
                 and polno = a.polno) addprem, --
              a.insuyear,
              a.insuyearflag,
              a.payyears,
              a.insuredappage,
              a.insuredsex,
              a.insuredno,
              a.payendyear,
              a.payendyearflag,
              a.getyear,
              a.signdate,
              a.cvalidate,
              a.insuredbirthday,
              a.bonusgetmode       
      FROM lcpol a, lmriskapp c, lccontstate b 
       WHERE b.startdate <= to_date('20101130', 'YYYYMMDD')
         AND b.startdate = (SELECT MAX(t.startdate) FROM lccontstate t
                              WHERE t.polno = a.polno  
                              AND t.statetype = 'Available' 
                              AND t.state = '0' 
                              AND t.enddate is null 
                              AND t.startdate <= to_date('20101130', 'YYYYMMDD'))
         AND NOT EXISTS(SELECT 'X' FROM lmriskapp t 
                          WHERE t.riskprop   = 'G'       --
                            AND t.riskperiod = 'L'       --
                            AND t.risktype  IN ('L', 'H')--
                            AND t.riskcode = a.riskcode) 
         AND a.riskcode IN (SELECT t.riskcode FROM riskparam t)
         AND a.polno    = b.polno
         AND a.contno   = b.contno
         AND a.riskcode = c.riskcode
         AND c.kindcode NOT LIKE 'U' 
         AND (b.statetype = 'Available' AND b.state = '0' AND b.enddate is null)
         AND a.signdate between to_date('20050101', 'YYYYMMDD') 
                          and to_date('20101130', 'YYYYMMDD')可能是含有max的子查询比较花时间，应该如何改进？

Answer 1

这是执行计划,我看不懂...SELECT STATEMENT, GOAL = ALL_ROWS Optimizer=ALL_ROWS Cardinality=1 Bytes=198 Cost=44993 CPU cost=5915775297 IO cost=44138
 SORT AGGREGATE Cardinality=1 Bytes=26
  FILTER
   TABLE ACCESS BY INDEX ROWID Optimizer=ANALYZED Object name=LCDUTY Cardinality=1 Bytes=26 Cost=2 CPU cost=11791 IO cost=2
    INDEX RANGE SCAN Optimizer=ANALYZED Object name=PK_LCDUTY Cardinality=1 Cost=1 CPU cost=8827 IO cost=1
 SORT AGGREGATE Cardinality=1 Bytes=22
  TABLE ACCESS BY INDEX ROWID Optimizer=ANALYZED Object name=LCPREM Cardinality=1 Bytes=22 Cost=2 CPU cost=12004 IO cost=2
   INDEX RANGE SCAN Optimizer=ANALYZED Object name=IDX_LCPREM_POL Cardinality=2 Cost=1 CPU cost=8706 IO cost=1
 TABLE ACCESS BY INDEX ROWID Optimizer=ANALYZED Object name=LCCONTSTATE Cardinality=1 Bytes=51 Cost=1 CPU cost=9082 IO cost=1
  NESTED LOOPS Cardinality=1 Bytes=198 Cost=44993 CPU cost=5915775297 IO cost=44138
   NESTED LOOPS Cardinality=1 Bytes=147 Cost=44992 CPU cost=5915766215 IO cost=44137
    NESTED LOOPS Cardinality=1 Bytes=142 Cost=44991 CPU cost=5915762842 IO cost=44136
     HASH JOIN RIGHT ANTI Cardinality=1 Bytes=137 Cost=44990 CPU cost=5915762422 IO cost=44135
      TABLE ACCESS FULL Optimizer=ANALYZED Object name=LMRISKAPP Cardinality=8 Bytes=80 Cost=3 CPU cost=77287 IO cost=3
      NESTED LOOPS Cardinality=54108 Bytes=6871716 Cost=44985 CPU cost=5906811940 IO cost=44132
       VIEW Object name=VW_SQ_1 Cardinality=54108 Bytes=1136268 Cost=44727 CPU cost=5513624508 IO cost=43931
        FILTER
         HASH GROUP BY Cardinality=54108 Bytes=3462912 Cost=44727 CPU cost=5513624508 IO cost=43931
          TABLE ACCESS FULL Optimizer=ANALYZED Object name=LCCONTSTATE Cardinality=1214630 Bytes=77736320 Cost=37481 CPU cost=4245668773 IO cost=36868
       TABLE ACCESS BY INDEX ROWID Optimizer=ANALYZED Object name=LCPOL Cardinality=1 Bytes=106 Cost=1 CPU cost=7267 IO cost=1
        INDEX UNIQUE SCAN Optimizer=ANALYZED Object name=PK_LCPOL Cardinality=1 Cost=1 CPU cost=3609 IO cost=1
     INDEX UNIQUE SCAN Optimizer=ANALYZED Object name=PK_RISKCODE Cardinality=1 Bytes=5 Cost=1 CPU cost=420 IO cost=1
    TABLE ACCESS BY INDEX ROWID Optimizer=ANALYZED Object name=LMRISKAPP Cardinality=1 Bytes=5 Cost=1 CPU cost=3373 IO cost=1
     INDEX UNIQUE SCAN Optimizer=ANALYZED Object name=PK_LMRISKAPP Cardinality=1 Cost=1 CPU cost=420 IO cost=1
   INDEX RANGE SCAN Optimizer=ANALYZED Object name=PK_INTERFACE_STATE Cardinality=1 Cost=1 CPU cost=6117 IO cost=1

Answer 2

SELECT MAX(t.startdate) FROM lccontstate t
                              WHERE t.polno = a.polno  
                              AND t.statetype = 'Available' 
                              AND t.state = '0' 
                              AND t.enddate is null 
                              AND t.startdate <= to_date('20101130', 'YYYYMMDD')走了全扫enddate is null,肯定是全扫，无法避免的

Answer 3

enddate is null 不一定就是全表扫描，LZ把 AND a.riskcode IN (SELECT t.riskcode FROM riskparam t)改成:
and exists (select 1 from riskparam t where t.riskcode  = a.riskcode )另外，其他字段上建复合索引！

Answer 4

SELECT MAX(t.startdate) FROM lccontstate t
                              WHERE t.polno = a.polno  
                              AND t.statetype = 'Available' 
                              AND t.state = '0' 
                              AND t.enddate is null 
                              AND t.startdate <= to_date('20101130', 'YYYYMMDD')
SELECT 'X' FROM lmriskapp t 
                          WHERE t.riskprop   = 'G'       --
                            AND t.riskperiod = 'L'       --
                            AND t.risktype  IN ('L', 'H')--
                            AND t.riskcode = a.riskcode两个语句是走全表扫描的。不知道LZ这两表的数据量。如果数据比较少全表也没有关系。如果比较多·那就建个索引什么的或者改写SQL
 AND c.kindcode NOT LIKE 'U' 这个与
 AND c.kindcode != 'U' 
无区别吧！改改 改成=来做吧！

Answer 5

关键还是看你的表的数据量·还有LZ需要返回的结果数量~
如果表数据量不大，需要返回所有合法结果的改成 HASH_JOIN吧！可能会比NL好些！

Answer 6

and exists (select 1 from riskparam t where t.riskcode = a.riskcode )
这个我换过了，没发现有什么变化
我这个riskparam 是个参数表只有40条记录左右
lcpol 这个表是主表的记录数有500多万
lccontstate 这个有800多万
相应的符合索引已经有了，但是发现将符合索引字段放到where条件后面比不是符合索引的还慢
我那个max子查询还能用别的写法让它快一点吗？
lccontstate表建什么样的索引好？

Answer 7

is not null用不上索引，不是全扫还能怎么玩？

Answer 8

原SQL问题很多。首先大表之间的笛卡尔积就会非常慢，    
类似这句 FROM lcpol a, lmriskapp c, lccontstate b 处理数据会以表大小的乘积形式增加。其次逻辑也不清晰，很明显b表的查询条件存在重复和不必要的逻辑。（startdate的MAX根本不必要 ）建议是先能在各个表做独自查询的先独立查询，用子查询的结果集再进行表之间的联查。
select       a.contno,
             a.polno,
             a.riskcode,
             a.managecom,
             a.salechnl,
             1 pol_state,
             a.amnt standamnt,
             a.prem,
             (select NVL(sum(amnt), 0) 
                from lcduty
               where polno = a.polno
                 AND a.bonusgetmode = '4'
                 and length(dutycode) != 6
                 and Substr(dutycode, 7, 1) = '1') bonusamnt,--
             (select NVL(sum(prem), 0) 
                from lcprem
               where payplantype in ('01', '03')
                 and polno = a.polno) addprem, --
              a.insuyear,
              a.insuyearflag,
              a.payyears,
              a.insuredappage,
              a.insuredsex,
              a.insuredno,
              a.payendyear,
              a.payendyearflag,
              a.getyear,
              a.signdate,
              a.cvalidate,
              a.insuredbirthday,
              a.bonusgetmode        
from 
(select * from 
(select a.* from lcpol a 
where a.signdate between to_date('20050101', 'YYYYMMDD') and to_date('20101130', 'YYYYMMDD')
and exists(select * FROM riskparam t where t.riskcode = a.riskcode) left join(
(select * from lmriskapp ) 
minus
(select * from lmriskapp where kindcode LIKE 'U' or (riskprop = 'G' and AND riskperiod = 'L' AND risktype  IN ('L', 'H'))
) c 
on a.riskcode = c.riskcode) a 
left join( 
(select * from lccontstate b where b.statetype = 'Available' 
                              AND b.state = '0' 
                              AND b.enddate is null 
      AND b.startdate <= to_date('20101130', 'YYYYMMDD')
) b on a.polno = b.polno AND a.contno = b.contno

Answer 9

我都已经指定了连接条件，应该不是笛卡尔积了啊？
lcpol 跟 lccontstate 通过polno关联，是一对多的关系 
比如polno = 123 这个账号在startdate = 11月28日 状态为：
  b.statetype = 'Available'  
  AND b.state = '0'  
  AND b.enddate is null
在startdate = 11月29日 状态为：
  b.statetype = 'Available'  
  AND b.state = '1'  
  AND b.enddate = 2010-11-28
在 startdate = 11月30日状态为：
  b.statetype = 'Available'  
  AND b.state = '0'  
  AND b.enddate is null
这样如果不用max的话，相同的条件为
  b.statetype = 'Available'  
  AND b.state = '0'  
  AND b.enddate is null
在lccontstate 表中有多条重复记录
而且我需要的是离11月30日最近的状态为：
  b.statetype = 'Available'  
  AND b.state = '0'  
  AND b.enddate is null
的记录