能不能请各位大哥帮帮忙,帮看一下
update WNSD A set LASTNBR=(select max(Right(B.CENTITYID,2)) MAX
LASTNBR from WLGT B where ((((B.FABID=A.FABID and SUBSTR(B.CENT
ITYID,1,(length(B.CENTITYID)-3))=A.PREFIX) and B.FABID=:b0) and
A.KIND='SL') and TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')=
:b1)) where exists (select 1 from WLGT B where ((((B.FABID=A.FA
BID and SUBSTR(B.CENTITYID,1,(length(B.CENTITYID)-3))=A.PREFIX)
and B.FABID=:b0) and A.KIND='SL') and TO_CHAR(B.CHKTIME,'YYYY-MM
-DD HH24:MI:SS.FF6')=:b1))
update WNSD A set LASTNBR=(select max(Right(B.CENTITYID,2)) MAX
LASTNBR from WLGT B where ((((B.FABID=A.FABID and SUBSTR(B.CENT
ITYID,1,(length(B.CENTITYID)-3))=A.PREFIX) and B.FABID=:b0) and
A.KIND='SL') and TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')=
:b1)) where exists (select 1 from WLGT B where ((((B.FABID=A.FA
BID and SUBSTR(B.CENTITYID,1,(length(B.CENTITYID)-3))=A.PREFIX)
and B.FABID=:b0) and A.KIND='SL') and TO_CHAR(B.CHKTIME,'YYYY-MM
-DD HH24:MI:SS.FF6')=:b1))
你要把你得sql写的工整一些,否则没有人愿意帮你看
2
to_char(b.chktime, 'YYYY-MM-DD HH24:MI:SS.FF6') = :b1
=>
b.chktime = to_date(:b1,'YYYY-MM-DD HH24:MI:SS.FF6')
3
两个表的fabid都要有索引
4
其他的,比如analyze table, 看explain,等等
SET LASTNBR =
(SELECT MAX(Right(B.CENTITYID,1)) AS MAXLASTNBR
FROM WLGT B
WHERE B.FABID = A.FABID
AND SUBSTR(B.CENTITYID,1,length(B.CENTITYID)-2) = A.PREFIX
AND SUBSTR(B.CENTITYID,11,1)='.' AND B.FABID = :sqlFabID19B
AND A.KIND='SL'
AND TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')= :sqlChkTime19B)
WHERE Exists (SELECT 1 FROM WLGT B
WHERE B.FABID = A.FABID
AND SUBSTR(B.CENTITYID,1,length(B.CENTITYID)-2) = A.PREFIX
AND SUBSTR(B.CENTITYID,11,1)='.'
AND B.FABID = :sqlFabID19B AND A.KIND='SL'
AND TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6') = :sqlChkTime19B);
WNSD 的Index为:FABID,KIND,PREFIX
WLGT 的Index为:FABID,CHKTIME
在这个sql语句中,CHKTIME的索引根本没有被使用到
应当对表达式
TO_CHAR(CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')
作函数索引同样的,也要对表达式
SUBSTR(CENTITYID,1,(length(CENTITYID)-3))
SUBSTR(B.CENTITYID,11,1)
作函数索引另外:
如果WLGT和WNSD都很大
Exists 会比较慢这时可以考虑用join替换Exists
但oracle不支持在update语句中直接使用join
所以,应当把这个sql语句改写为cursor形式
例如,用存储过程可以写成CURSOR C
IS
SELECT A.ROWID AS ROW_ID
FROM
WNSD A,
WLGT B
WHERE
B.FABID=A.FABID
and SUBSTR(B.CENTITYID,1,(length(B.CENTITYID)-3))=A.PREFIX
and B.FABID=:b0
and A.KIND='SL'
and TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')=:b1
;BEGIN
FOR I IN C
LOOP
UPDATE WNSD A
SET LASTNBR = (
SELECT MAX(Right(B.CENTITYID,2)) MAXLASTNBR
FROM WLGT B
WHERE
B.FABID=A.FABID
and SUBSTR(B.CENTITYID,1,(length(B.CENTITYID)-3))=A.PREFIX
and B.FABID=:b0
and A.KIND='SL'
and TO_CHAR(B.CHKTIME,'YYYY-MM-DD HH24:MI:SS.FF6')= :b1
)
WHERE A.ROWID = I.ROW_ID;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;