select (select bmdm_name from businessAreaConcentrade where bmdm = d.companyid) companyid,
to_char(d.endorOverDate,'yyyy-mm') year_month,s.scaleofdes,
count(distinct d.claimno) countnum
from (
select claimno,
substr(','||tt.CHECKSCALEOFMARK,
instr(','||tt.CHECKSCALEOFMARK,',',1,temp.rn)+1,instr(tt.CHECKSCALEOFMARK,',',1,temp.rn)
-instr(','||tt.CHECKSCALEOFMARK,',',1,temp.rn)) CHECKSCALEOFMARK,
checkdate,ENDORSCALEOFMARK,ENDORDATE
from claimScaleOfs tt,
(select rownum rn from dual connect by rownum<35)temp
where length(tt.CHECKSCALEOFMARK)-length(replace(tt.CHECKSCALEOFMARK,','))>=temp.rn
--order by 1,temp.rn
) c,scaleOfsPara s,claiminfo d
where c.claimno=d.claimno
and c.checkScaleOf=s.id
and (trunc(d.endorOverDate,'DD') >= to_date('2012-01-01','yyyy/mm/dd') and trunc(d.endorOverDate,'DD') <= to_date('2012-05-02','yyyy/mm/dd') )
and (c.checkScaleOf is not null) or (c.endorScaleOf is not null)
and s.score<=0
group by d.companyid,to_char(d.endorOverDate,'yyyy-mm'),s.scaleofdes
order by d.companyid,to_char(d.endorOverDate,'yyyy-mm'),s.scaleofdes上面语句执行比较慢,好像是慢在表连接。。各位大侠看看有没有可以优化的
这个应该挺影响速度的,如果这个2个字段有索引的话还是把or替换为unioncompanyid,endorOverDate,scaleofdes尝试在这3个字段上建立组合索引参考资料:
http://wenku.baidu.com/view/c631bd6327d3240c8447ef4e.html
http://blog.itpub.net/post/43362/523249