随能帮我优化一下这段ORACLE更新语句
0
悬赏园豆：100
[已关闭问题]
UPDATE CIS.NEW_QPS_PROJECT_FEE_DETAIL T
SET T.ESTIMATE_VALUE =
(SELECT NVL(SUM(T1.ESTIMATE_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
CHECK_VALUE =
(SELECT NVL(SUM(T1.CHECK_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
BUDGETARY_VALUE =
(SELECT NVL(SUM(T1.BUDGETARY_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
ESTIMATE_RELY =
(SELECT NVL(SUM(T1.ESTIMATE_RELY*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
FINALLY_ESTIMATE_VALUE =
(SELECT NVL(SUM(T1.FINALLY_ESTIMATE_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
CONTRACT_VALUE =
(SELECT NVL(SUM(T1.CONTRACT_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
CHANGE_VALUE =
(SELECT NVL(SUM(T1.CHANGE_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID),
SETTLEMENT_VALUE =
(SELECT NVL(SUM(T1.SETTLEMENT_VALUE*T2.RATE),0)
FROM CIS.NEW_QPS_PROJECT_FEE_SUB T1 LEFT JOIN CIS.NEW_QPS_PROJECT_EXCHANGE_RATE T2 ON T1.COIN_KIND=T2.KIND
WHERE T.FEEDETAIL_PKID=T1.FEEDETAIL_PKID AND T.PROJECT_ID=T2.PROJECT_ID)
WHERE EXISTS
(SELECT 1 FROM CIS.NEW_QPS_FEECODE T1 WHERE T.FEE_ID=T1.FEE_ID AND T1.HAS_CHILD='N') And T.PROJECT_ID='0001'
问题补充：
执行计划如下：
UPDATE STATEMENT, GOAL = ALL_ROWS 55 651 48825
UPDATE CIS NEW_QPS_PROJECT_FEE_DETAIL
HASH JOIN RIGHT SEMI 55 651 48825
TABLE ACCESS FULL CIS NEW_QPS_FEECODE 3 569 6259
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_DETAIL 52 651 41664
SORT AGGREGATE 1 27
FILTER
HASH JOIN OUTER 23 26 702
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 13
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 25
FILTER
HASH JOIN OUTER 23 26 650
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 11
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 26
FILTER
HASH JOIN OUTER 23 26 676
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 12
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 29
FILTER
HASH JOIN OUTER 23 26 754
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 15
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 29
FILTER
HASH JOIN OUTER 23 26 754
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 15
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 29
FILTER
HASH JOIN OUTER 23 26 754
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 15
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 25
FILTER
HASH JOIN OUTER 23 26 650
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 11
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
SORT AGGREGATE 1 25
FILTER
HASH JOIN OUTER 23 26 650
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_FEE_SUB 19 1 11
TABLE ACCESS FULL CIS NEW_QPS_PROJECT_EXCHANGE_RATE 3 90 1260
其他回答(4)
0
看了感到怕怕，祝早日解决
0
头晕,发现都是取一个联合查询的最大值,你可以自定义一个函数,将最大值取出来,然后再分割字符串,取出最大值,来更新
0
太头痛了,这个你可以先了解这一段代码是干嘛用的,自己重构一遍,不要看现有的了,根据需求自己写!
0
先把所有的要更新的数据SELECT一个TABLE，之后再用TABLE与UPDATE—TABLE进行关联更新吧，感觉会高点，最少不会UPDATE每个字段就统计一次数据。
0
哎哟,看来我写的那些SQL语句算是小鸟了.......
