首页 新闻 赞助 找找看

随能帮我优化一下这段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
cleverboy的主页 cleverboy | 初学一级 | 园豆:100
提问于:2008-09-11 09:06
< >
分享
其他回答(4)
0

看了感到怕怕,祝早日解决

luotong | 园豆:4 (初学一级) | 2008-09-11 10:43
0

头晕,发现都是取一个联合查询的最大值,你可以自定义一个函数,将最大值取出来,然后再分割字符串,取出最大值,来更新

zjy | 园豆:3194 (老鸟四级) | 2008-09-11 11:31
0

太头痛了,这个你可以先了解这一段代码是干嘛用的,自己重构一遍,不要看现有的了,根据需求自己写!

飞阿飞 | 园豆:444 (菜鸟二级) | 2008-09-11 11:35
0

先把所有的要更新的数据SELECT一个TABLE,之后再用TABLE与UPDATE—TABLE进行关联更新吧,感觉会高点,最少不会UPDATE每个字段就统计一次数据。

深水的猫 | 园豆:110 (初学一级) | 2008-09-11 13:00
0

哎哟,看来我写的那些SQL语句算是小鸟了.......

egmkang | 园豆:-734 (初学一级) | 2008-09-12 23:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册