oracle优化问题
某系统一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