你可以尝试用merge 改写下,应该会快些,不过要想更快的话,就需要看执行计划了,我大概写了一下,因为没有模拟数据,所以不保证完全正确,你可以调试下。 MERGE INTO FACILITY f USING (SELECT b.ACCT_NAT_KEY, b.CMMT_AMT FROM AUTOGRADE_MAIN b WHERE b.CMMT_AMT IN (SELECT ire.CMMT_AMT FROM INFEED_RECORDS ire WHERE ire.ACCT_NAT_KEY <> ire.ULT_PRT_ACCT_NBR AND ire.COMT_AMT <> 0 AND ire.LEGAL_FLAG = 'N' UNION SELECT a.CMMT_AMT FROM AUTOGRADE_MAIN a)) c ON (a.ACCT_NAT_KEY = f.ACCT_NAT_KEY) WHEN MATCHED THEN UPDATE SET f.SOURCE_SYS_BALANCE = c.ACCT_NAT_KEY
不好意思 刚才改写错了 MERGE INTO FACILITY f USING (SELECT b.ACCT_NAT_KEY, b.CMMT_AMT FROM AUTOGRADE_MAIN b WHERE b.CMMT_AMT IN (SELECT ire.CMMT_AMT FROM INFEED_RECORDS ire WHERE ire.ACCT_NAT_KEY <> ire.ULT_PRT_ACCT_NBR AND ire.COMT_AMT <> 0 AND ire.LEGAL_FLAG = 'N') AND b.CMMT_AMT IN (SELECT a.CMMT_AMT FROM AUTOGRADE_MAIN a)) c ON (a.ACCT_NAT_KEY = f.ACCT_NAT_KEY) WHEN MATCHED THEN UPDATE SET f.SOURCE_SYS_BALANCE = c.ACCT_NAT_KEY 我吧子查询里面的逻辑搞错了~~,你用这个调试吧
不过大概看一下,你能加索引的地方也就是:
AUTOGRADE_MAIN(ACCT_NAT_KEY)
AUTOGRADE_MAIN(ACCT_NAT_KEY,CMMT_AMT)
INFEED_RECORDS(ULT_PRT_ACCT_NBR)
INFEED_RECORDS(ULT_PRT_ACCT_NBR,LEGAL_FLAG)
FACILITY(f.ACCT_NAT_KEY)
如果是测试库,上面的索引都加上,然后如果速度有提升,就看执行计划,把没用到的索引再删了,哈~
MERGE INTO FACILITY f
USING (SELECT b.ACCT_NAT_KEY, b.CMMT_AMT
FROM AUTOGRADE_MAIN b
WHERE b.CMMT_AMT IN
(SELECT ire.CMMT_AMT
FROM INFEED_RECORDS ire
WHERE ire.ACCT_NAT_KEY <> ire.ULT_PRT_ACCT_NBR
AND ire.COMT_AMT <> 0
AND ire.LEGAL_FLAG = 'N'
UNION
SELECT a.CMMT_AMT FROM AUTOGRADE_MAIN a)) c
ON (a.ACCT_NAT_KEY = f.ACCT_NAT_KEY)
WHEN MATCHED THEN
UPDATE SET f.SOURCE_SYS_BALANCE = c.ACCT_NAT_KEY
MERGE INTO FACILITY f
USING (SELECT b.ACCT_NAT_KEY, b.CMMT_AMT
FROM AUTOGRADE_MAIN b
WHERE b.CMMT_AMT IN (SELECT ire.CMMT_AMT
FROM INFEED_RECORDS ire
WHERE ire.ACCT_NAT_KEY <> ire.ULT_PRT_ACCT_NBR
AND ire.COMT_AMT <> 0
AND ire.LEGAL_FLAG = 'N')
AND b.CMMT_AMT IN (SELECT a.CMMT_AMT FROM AUTOGRADE_MAIN a)) c
ON (a.ACCT_NAT_KEY = f.ACCT_NAT_KEY)
WHEN MATCHED THEN
UPDATE SET f.SOURCE_SYS_BALANCE = c.ACCT_NAT_KEY
我吧子查询里面的逻辑搞错了~~,你用这个调试吧