SQL运行得太慢了，求解决办法

Question

上面的SQL未排版好，不大好看，现在我整理了下：
insert into ft_remit(date_id,area_id,businesstype_id,dept_id,variety_id,remitstatus_id,amount_scope_id,DOMICILE_ID,amount,handlecharge,remitfee,notefee,remicode_id)
      select b.date_id,c.area_id,d.businesstype_id,e.dept_id,11,f.remitstatus_id,
             case when a.impremiamt<=200 then 1
                  when a.impremiamt>200 and a.impremiamt<=500 then 2
                  when a.impremiamt>500 and a.impremiamt<=1000 then 3
                  when a.impremiamt>1000 and a.impremiamt<=2000 then 4
                  when a.impremiamt>2000 and a.impremiamt<=5000 then 5
                  when a.impremiamt>5000 and a.impremiamt<=10000 then 6
                  when a.impremiamt>10000 and a.impremiamt<=20000 then 7
                  when a.impremiamt>20000 then 8 end,                                                                                                           
             case when substr(a.imppayeepostcode,4,1)='0' then 1
                  when substr(a.imppayeepostcode,5,2)='00' then 3
                  else 4 end,                                                                                                     
             a.impremiamt,
             case when a.impremidate<'20040501' then decode(a.impclassid,'00',0,'01',0,'02',5,'03',10)
                  else decode(a.impclassid,'00',0,'01',0,'02',3,'03',5) end,
             a.impremifee,                                         
             case when a.impremidate<='20030829' or a.impcharnum<=6 then 0
                  else (a.impcharnum-6)*0.1 end,
             a.remicode_id    
             from TM_hasarea_EXIMPREMITTB a,pbl_sd_time b,sd_area c,sd_businesstype d,sd_dept e,sd_remitstatus f,postcode_rel g,ORGID_OPT_ID h
                  where a.impremidate=to_char(b.the_date,'yyyymmdd') and 
                        substr(a.imppayeepostcode,1,4)=g.postcode and
                        a.impclassid=d.Business_Code and
                        a.imporgid=e.REMIT_ORGID and
                        a.impremistate=f.REMI_CODE and 
                        a.impoprid=h.OPT_ID and 
                        h.ORGID=e.REMIT_ORGID and 
                        c.postcode=g.postcode_rel and
                        e.dept_id=h.dept_id and 
                        e.commission_id=1

Answer 1

create index idx_imppayeepostcode on TM_hasarea_EXIMPREMITTB 
(substr(a.imppayeepostcode,1,4));
试试

Answer 2

where a.impremidate=to_char(b.the_date,'yyyymmdd') and 
                        substr(a.imppayeepostcode,1,4)=g.postcode and
                        a.impclassid=d.Business_Code and
                        a.imporgid=e.REMIT_ORGID and
                        a.impremistate=f.REMI_CODE and 
                        a.impoprid=h.OPT_ID and 
                        h.ORGID=e.REMIT_ORGID and 
                        c.postcode=g.postcode_rel and
                        e.dept_id=h.dept_id and 
                        e.commission_id=1这些字段是否建立有索引！看下这条语句的执行计划，索引使用的情况

Answer 3

把
 TM_hasarea_EXIMPREMITTB a,pbl_sd_time b,sd_area c,sd_businesstype d,sd_dept e,sd_remitstatus f,postcode_rel g,ORGID_OPT_ID h
                  where a.impremidate=to_char(b.the_date,'yyyymmdd') and 
                        substr(a.imppayeepostcode,1,4)=g.postcode and
                        a.impclassid=d.Business_Code and
                        a.imporgid=e.REMIT_ORGID and
                        a.impremistate=f.REMI_CODE and 
                        a.impoprid=h.OPT_ID and 
                        h.ORGID=e.REMIT_ORGID and 
                        c.postcode=g.postcode_rel and
                        e.dept_id=h.dept_id and 
                        e.commission_id=1单独查一下，看速度怎么样，如果还可以的话，这么长的查询存到一个临时建的表中去。比如 AAAA
insert into ft_remit(date_id,area_id,businesstype_id,dept_id,variety_id,remitstatus_id,amount_scope_id,DOMICILE_ID,amount,handlecharge,remitfee,notefee,remicode_id)
      select b.date_id,c.area_id,d.businesstype_id,e.dept_id,11,f.remitstatus_id,
             case when a.impremiamt<=200 then 1
                  when a.impremiamt>200 and a.impremiamt<=500 then 2
                  when a.impremiamt>500 and a.impremiamt<=1000 then 3
                  when a.impremiamt>1000 and a.impremiamt<=2000 then 4
                  when a.impremiamt>2000 and a.impremiamt<=5000 then 5
                  when a.impremiamt>5000 and a.impremiamt<=10000 then 6
                  when a.impremiamt>10000 and a.impremiamt<=20000 then 7
                  when a.impremiamt>20000 then 8 end,                                                                                                           
             case when substr(a.imppayeepostcode,4,1)='0' then 1
                  when substr(a.imppayeepostcode,5,2)='00' then 3
                  else 4 end,                                                                                                     
             a.impremiamt,
             case when a.impremidate<'20040501' then decode(a.impclassid,'00',0,'01',0,'02',5,'03',10)
                  else decode(a.impclassid,'00',0,'01',0,'02',3,'03',5) end,
             a.impremifee,                                         
             case when a.impremidate<='20030829' or a.impcharnum<=6 then 0
                  else (a.impcharnum-6)*0.1 end,
             a.remicode_id    
             from AAA大概意思就是通过一个中间表，来减少一下insert 的速度。。
偶一般都是这么做的不知道可用不。

Answer 4

                        a.impclassid=d.Business_Code and
                        a.imporgid=e.REMIT_ORGID and
                        a.impremistate=f.REMI_CODE and 
                        a.impoprid=h.OPT_ID and 
                        h.ORGID=e.REMIT_ORGID and 
                        c.postcode=g.postcode_rel and
                        e.dept_id=h.dept_id and 
                        e.commission_id=1条件中的关联字段上有索引吗?建议查看看一下,然后看一下select的执行计划,看看是不是使用了索引.

Answer 5

你的select语句永远是全表扫描，是否可以考虑其他方法:
1. 使用表分区（TM_hasarea_EXIMPREMITTB.impremiamt）
2. 增加冗余字段减少case、decode和where中的函数