1、sql 代码
UPDATE EAS1.T_AP_OTHERBILL SET CFCOMPANYTYPE=‘mz’ WHERE FID IN ( SELECT A.FID FROM EAS1.T_AP_OTHERBILL A LEFT JOIN EAS1.T_ORG_COMPANY B ON A.FCOMPANYID =B.FID WHERE A.CFCOMPANYTYPE IS NULL AND TO_CHAR(A.FBIZDATE,‘yyyy-mm-dd’)>‘2019-07-01’ AND (INSTR(B.FNUMBER,‘MZ’)> 0 OR INSTR(B.FNUMBER,‘YY’)> 0 OR INSTR(B.FNUMBER,‘ZH’)> 0 ))
2、执行计划
截图发现，看不清
and TO_CHAR(A.FBIZDATE, ‘yyyy-mm-dd’)>‘2019-07-01 ’ -- 你这种写法，如果A.FBIZDATE有索引，也不会走，需要改成： A.FBIZDATE>to_date('2019-07-01','yyyy-mm-dd')
and (INSTR(B.FNUMBER,‘MZ’)> 0 or INSTR(B.FNUMBER,‘YY’)> 0 or INSTR(B.FNUMBER,‘ZH’)> 0 )) -- B.FNUMBER 加索引或函数索引，你试一下，看看哪个效果好点
复制
评论
有用 1
首先要建索引。
create indx idx1 on t_otherbill(fbizdate,cfcompanytype);
create indx idx2 on t_org_company(fnumber);
查询语句要改写，把where放到left join之前，比如with t as (select xxx from t_otherbill where FBIZDATE>to_date('2019-07-01',‘yyyy-mm-dd) and CFCOMPANYTYPE IS NULL), s as (select ... from t_org_company where INSTR(B.FNUMBER,‘MZ’)> 0 OR INSTR(B.FNUMBER,‘YY’)> 0 OR INSTR(B.FNUMBER,‘ZH’)> 0) select fid from t left join s on t.FCOMPANYID =s.FID
其实有时候，光帖SQL语句没有多大用，要真正解决问题，首先要说明相关表的定义，然后这个查询要实现什么业务逻辑，这样才能更准确地改善。看个大概的话，只能是建议加索引
评论 有用 1