某系统一sql如下,其执行计划的cost为2600多,才接触oralce,
大家帮顺便看看下面的sql语句有哪些不足,哪些优化建议吧,谢谢。
SELECT distinct b.kczwmc, a.xkkh FROM xsxkb a,kcdmbview b ,jxrwbview c WHERE a.xh=:paramstr1 and a.xn||a.xq IN (SELECT xn||xq FROM pjxqszb WHERE pjkg='0') AND (c.xkkh=a.xkkh) and (c.sfkpj='是') and (a.sfkp='是' or a.sfkp is null) and substr(a.xkkh,case when instr(a.xkkh,'-',1,3)<>0 then instr(a.xkkh,'-',1,3) else length(a.xkkh)+1 end+1,case when instr(a.xkkh,'-',1,4)<>0 then instr(a.xkkh,'-',1,4) else length(a.xkkh)+1 end-(case when instr(a.xkkh,'-',1,3)<>0 then instr(a.xkkh,'-',1,3) else length(a.xkkh)+1 end+1))=b.kcdm and b.bs1 is not null and b.bs1 not like '''' AND (substr(a.xkkh,length(a.xkkh),1)<='9' or ascII(substr(a.xkkh,length(a.xkkh),1))>=97) and a.xkkh not in( select xkkh from xspfb_200920102 where xn||xq =(select xn||xq from pjxqszb where pjkg='0') and xh=:paramstr2 and sfjf='1' ) ORDER BY kczwmc
大家帮顺便看看下面的sql语句有哪些不足,哪些优化建议吧,谢谢。
SELECT distinct b.kczwmc, a.xkkh FROM xsxkb a,kcdmbview b ,jxrwbview c WHERE a.xh=:paramstr1 and a.xn||a.xq IN (SELECT xn||xq FROM pjxqszb WHERE pjkg='0') AND (c.xkkh=a.xkkh) and (c.sfkpj='是') and (a.sfkp='是' or a.sfkp is null) and substr(a.xkkh,case when instr(a.xkkh,'-',1,3)<>0 then instr(a.xkkh,'-',1,3) else length(a.xkkh)+1 end+1,case when instr(a.xkkh,'-',1,4)<>0 then instr(a.xkkh,'-',1,4) else length(a.xkkh)+1 end-(case when instr(a.xkkh,'-',1,3)<>0 then instr(a.xkkh,'-',1,3) else length(a.xkkh)+1 end+1))=b.kcdm and b.bs1 is not null and b.bs1 not like '''' AND (substr(a.xkkh,length(a.xkkh),1)<='9' or ascII(substr(a.xkkh,length(a.xkkh),1))>=97) and a.xkkh not in( select xkkh from xspfb_200920102 where xn||xq =(select xn||xq from pjxqszb where pjkg='0') and xh=:paramstr2 and sfjf='1' ) ORDER BY kczwmc
2:a.sfkp='是' or a.sfkp is null 事先实现NVL(a.sfkp,'是')
(2)用EXISTS替换DISTINCT
(3)sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行
(4)避免使用耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
(5)用WHERE替代ORDER BY
(6)用IN来替换OR
举个例子:
低效:
SELECT…. FROM table WHERE ID = 10 OR ID = 20 OR ID = 30
高效
SELECT… FROM table WHERE ID IN (10,20,30)
(7)用>=替代>
(8)
另外SQL语句查询慢大多是由不合理的架构引起的,楼主还是把精力放在架构上吧.
oracle总是先解析sql语句,把小写的字母转换成大写的再执行 ??
2,distinct 改成 group by
3,适当增加索引
4,改成大写(听别人这么说过会有效果)
5,选择正确的驱动表
6,避免在索引列上使用not
。
感觉楼主的设计有点复杂了,最好能改一下表的设计。
把执行计划贴出来看一下.. SQL也很乱. 看了头晕..