怎么修改比较好
SQ如下:
UPDATE t_sd_jmlpricepolicymaterials
SET fcurrenttotalnum = (NVL
((SELECT SUM(CASE WHEN (se.fbasestatus IN (3, 6, 7)) THEN se.fbaseqty ELSE se.fbaseqty END) TOTAL_NUM
FROM t_sd_saleorder SO, t_sd_saleorderentry SE WHERE ((((so.fid = se.fparentid
AND (se.fbasestatus NOT IN (-3, -2, -1, 0, 1)))
AND (NVL(se.FJmlReissue, 0) <> 1))
AND NOT EXISTS (SELECT 1 FROM t_ar_otherbill BILL, t_ar_otherbillentry BENTRY WHERE
((bill.fid = bentry.fparentid AND bentry.fcorebillid = so.fid)
AND (bill.fbillstatus IN (2, 3)))))
AND se.fpolicymentryfid = t_sd_jmlpricepolicymaterials.fid)
GROUP BY se.fpolicymentryfid), 0) / NVL((SELECT t_bd_measureunit.fcoefficient FROM t_bd_measureunit WHERE t_bd_measureunit.fid = t_sd_jmlpricepolicymaterials.fmaterialunitid), 1)) WHERE (fid IN ('VlFtK6y9T06WNueJ6Hb4JsEEaBY='))
Plan hash value: 1991201394
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 56 | 1 (0)| 00:00:01 |
| 1 | UPDATE | T_SD_JMLPRICEPOLICYMATERIALS | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SD_JMLPRICEPOLICYMATERIALS | 1 | 56 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_JMLPRICEPOLICYM | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT GROUP BY NOSORT | | 1 | 95 | 140 (1)| 00:00:02 |
| 5 | NESTED LOOPS | | 7 | 665 | 5 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T_SD_SALEORDERENTRY | 134 | 8844 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | JML_POLICYMATERIALID_ON_SALE | 66 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_SALEORDER_FID | 1 | 29 | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 7 | 630 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILLENTRY | 7 | 406 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | FCOREBILLID | 7 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILL | 1 | 32 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_AR_OTHERBILL | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | T_BD_MEASUREUNIT | 1 | 32 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MEASUREUNIT | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T_SD_JMLPRICEPOLICYMATERIALS@UPD$1
3 - UPD$1 / T_SD_JMLPRICEPOLICYMATERIALS@UPD$1
4 - SEL$1
6 - SEL$1 / SE@SEL$1
7 - SEL$1 / SE@SEL$1
8 - SEL$1 / SO@SEL$1
9 - SEL$2
10 - SEL$2 / BENTRY@SEL$2
11 - SEL$2 / BENTRY@SEL$2
12 - SEL$2 / BILL@SEL$2
13 - SEL$2 / BILL@SEL$2
14 - SEL$3 / T_BD_MEASUREUNIT@SEL$3
15 - SEL$3 / T_BD_MEASUREUNIT@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FID"='VlFtK6y9T06WNueJ6Hb4JsEEaBY=')
6 - filter(NVL("SE"."FJMLREISSUE",0)<>1 AND "SE"."FBASESTATUS"<>1 AND "SE"."FBASESTATUS"<>0 AND
"SE"."FBASESTATUS"<>(-1) AND "SE"."FBASESTATUS"<>(-2) AND "SE"."FBASESTATUS"<>(-3))
7 - access("SE"."FPOLICYMENTRYFID"=SYS_OP_C2C(:B1))
8 - access("SO"."FID"="SE"."FPARENTID")
filter( NOT EXISTS (SELECT 0 FROM "T_AR_OTHERBILLENTRY" "BENTRY","T_AR_OTHERBILL" "BILL" WHERE
"BILL"."FID"="BENTRY"."FPARENTID" AND ("BILL"."FBILLSTATUS"=2 OR "BILL"."FBILLSTATUS"=3) AND
"BENTRY"."FCOREBILLID"=:B1))
11 - access("BENTRY"."FCOREBILLID"=:B1)
12 - filter("BILL"."FBILLSTATUS"=2 OR "BILL"."FBILLSTATUS"=3)
13 - access("BILL"."FID"="BENTRY"."FPARENTID")
15 - access("T_BD_MEASUREUNIT"."FID"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=3; cmp=2,4) "SYS_ALIAS_2".ROWID[ROWID,10],
"T_SD_JMLPRICEPOLICYMATERIALS"."FMATERIALUNITID"[VARCHAR2,44], "FCURRENTTOTALNUM"[NUMBER,22],
"FID"[VARCHAR2,44]
3 - "SYS_ALIAS_2".ROWID[ROWID,10], "FID"[VARCHAR2,44]
4 - (#keys=1) "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], SUM("SE"."FBASEQTY")[22]
5 - (#keys=0) "SE".ROWID[ROWID,10], "SE"."FBASESTATUS"[NUMBER,22], "SE"."FBASEQTY"[NUMBER,22],
"SE"."FPARENTID"[VARCHAR2,44], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], "SE"."FJMLREISSUE"[NUMBER,22],
"SYS_ALIAS_1".ROWID[ROWID,10], "SO"."FID"[VARCHAR2,44]
6 - "SE".ROWID[ROWID,10], "SE"."FBASESTATUS"[NUMBER,22], "SE"."FBASEQTY"[NUMBER,22],
"SE"."FPARENTID"[VARCHAR2,44], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], "SE"."FJMLREISSUE"[NUMBER,22]
7 - "SE".ROWID[ROWID,10], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200]
8 - "SYS_ALIAS_1".ROWID[ROWID,10], "SO"."FID"[VARCHAR2,44]
9 - (#keys=0) "BENTRY".ROWID[ROWID,10], "BENTRY"."FPARENTID"[VARCHAR2,44],
"BENTRY"."FCOREBILLID"[VARCHAR2,44], "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44],
"BILL"."FBILLSTATUS"[NUMBER,22]
10 - "BENTRY".ROWID[ROWID,10], "BENTRY"."FPARENTID"[VARCHAR2,44], "BENTRY"."FCOREBILLID"[VARCHAR2,44]
11 - "BENTRY".ROWID[ROWID,10], "BENTRY"."FCOREBILLID"[VARCHAR2,44]
12 - "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44], "BILL"."FBILLSTATUS"[NUMBER,22]
13 - "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44]
14 - "T_BD_MEASUREUNIT".ROWID[ROWID,10], "T_BD_MEASUREUNIT"."FID"[VARCHAR2,44],
"T_BD_MEASUREUNIT"."FCOEFFICIENT"[NUMBER,22]
15 - "T_BD_MEASUREUNIT".ROWID[ROWID,10], "T_BD_MEASUREUNIT"."FID"[VARCHAR2,44]
-------------------------------------------------------------------------------
[ 本帖最后由 saintfl 于 2010-8-8 20:20 编辑 ]
SQ如下:
UPDATE t_sd_jmlpricepolicymaterials
SET fcurrenttotalnum = (NVL
((SELECT SUM(CASE WHEN (se.fbasestatus IN (3, 6, 7)) THEN se.fbaseqty ELSE se.fbaseqty END) TOTAL_NUM
FROM t_sd_saleorder SO, t_sd_saleorderentry SE WHERE ((((so.fid = se.fparentid
AND (se.fbasestatus NOT IN (-3, -2, -1, 0, 1)))
AND (NVL(se.FJmlReissue, 0) <> 1))
AND NOT EXISTS (SELECT 1 FROM t_ar_otherbill BILL, t_ar_otherbillentry BENTRY WHERE
((bill.fid = bentry.fparentid AND bentry.fcorebillid = so.fid)
AND (bill.fbillstatus IN (2, 3)))))
AND se.fpolicymentryfid = t_sd_jmlpricepolicymaterials.fid)
GROUP BY se.fpolicymentryfid), 0) / NVL((SELECT t_bd_measureunit.fcoefficient FROM t_bd_measureunit WHERE t_bd_measureunit.fid = t_sd_jmlpricepolicymaterials.fmaterialunitid), 1)) WHERE (fid IN ('VlFtK6y9T06WNueJ6Hb4JsEEaBY='))
Plan hash value: 1991201394
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 56 | 1 (0)| 00:00:01 |
| 1 | UPDATE | T_SD_JMLPRICEPOLICYMATERIALS | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SD_JMLPRICEPOLICYMATERIALS | 1 | 56 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_JMLPRICEPOLICYM | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT GROUP BY NOSORT | | 1 | 95 | 140 (1)| 00:00:02 |
| 5 | NESTED LOOPS | | 7 | 665 | 5 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T_SD_SALEORDERENTRY | 134 | 8844 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | JML_POLICYMATERIALID_ON_SALE | 66 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_SALEORDER_FID | 1 | 29 | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 7 | 630 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILLENTRY | 7 | 406 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | FCOREBILLID | 7 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILL | 1 | 32 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_AR_OTHERBILL | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | T_BD_MEASUREUNIT | 1 | 32 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MEASUREUNIT | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T_SD_JMLPRICEPOLICYMATERIALS@UPD$1
3 - UPD$1 / T_SD_JMLPRICEPOLICYMATERIALS@UPD$1
4 - SEL$1
6 - SEL$1 / SE@SEL$1
7 - SEL$1 / SE@SEL$1
8 - SEL$1 / SO@SEL$1
9 - SEL$2
10 - SEL$2 / BENTRY@SEL$2
11 - SEL$2 / BENTRY@SEL$2
12 - SEL$2 / BILL@SEL$2
13 - SEL$2 / BILL@SEL$2
14 - SEL$3 / T_BD_MEASUREUNIT@SEL$3
15 - SEL$3 / T_BD_MEASUREUNIT@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FID"='VlFtK6y9T06WNueJ6Hb4JsEEaBY=')
6 - filter(NVL("SE"."FJMLREISSUE",0)<>1 AND "SE"."FBASESTATUS"<>1 AND "SE"."FBASESTATUS"<>0 AND
"SE"."FBASESTATUS"<>(-1) AND "SE"."FBASESTATUS"<>(-2) AND "SE"."FBASESTATUS"<>(-3))
7 - access("SE"."FPOLICYMENTRYFID"=SYS_OP_C2C(:B1))
8 - access("SO"."FID"="SE"."FPARENTID")
filter( NOT EXISTS (SELECT 0 FROM "T_AR_OTHERBILLENTRY" "BENTRY","T_AR_OTHERBILL" "BILL" WHERE
"BILL"."FID"="BENTRY"."FPARENTID" AND ("BILL"."FBILLSTATUS"=2 OR "BILL"."FBILLSTATUS"=3) AND
"BENTRY"."FCOREBILLID"=:B1))
11 - access("BENTRY"."FCOREBILLID"=:B1)
12 - filter("BILL"."FBILLSTATUS"=2 OR "BILL"."FBILLSTATUS"=3)
13 - access("BILL"."FID"="BENTRY"."FPARENTID")
15 - access("T_BD_MEASUREUNIT"."FID"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=3; cmp=2,4) "SYS_ALIAS_2".ROWID[ROWID,10],
"T_SD_JMLPRICEPOLICYMATERIALS"."FMATERIALUNITID"[VARCHAR2,44], "FCURRENTTOTALNUM"[NUMBER,22],
"FID"[VARCHAR2,44]
3 - "SYS_ALIAS_2".ROWID[ROWID,10], "FID"[VARCHAR2,44]
4 - (#keys=1) "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], SUM("SE"."FBASEQTY")[22]
5 - (#keys=0) "SE".ROWID[ROWID,10], "SE"."FBASESTATUS"[NUMBER,22], "SE"."FBASEQTY"[NUMBER,22],
"SE"."FPARENTID"[VARCHAR2,44], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], "SE"."FJMLREISSUE"[NUMBER,22],
"SYS_ALIAS_1".ROWID[ROWID,10], "SO"."FID"[VARCHAR2,44]
6 - "SE".ROWID[ROWID,10], "SE"."FBASESTATUS"[NUMBER,22], "SE"."FBASEQTY"[NUMBER,22],
"SE"."FPARENTID"[VARCHAR2,44], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200], "SE"."FJMLREISSUE"[NUMBER,22]
7 - "SE".ROWID[ROWID,10], "SE"."FPOLICYMENTRYFID"[NVARCHAR2,200]
8 - "SYS_ALIAS_1".ROWID[ROWID,10], "SO"."FID"[VARCHAR2,44]
9 - (#keys=0) "BENTRY".ROWID[ROWID,10], "BENTRY"."FPARENTID"[VARCHAR2,44],
"BENTRY"."FCOREBILLID"[VARCHAR2,44], "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44],
"BILL"."FBILLSTATUS"[NUMBER,22]
10 - "BENTRY".ROWID[ROWID,10], "BENTRY"."FPARENTID"[VARCHAR2,44], "BENTRY"."FCOREBILLID"[VARCHAR2,44]
11 - "BENTRY".ROWID[ROWID,10], "BENTRY"."FCOREBILLID"[VARCHAR2,44]
12 - "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44], "BILL"."FBILLSTATUS"[NUMBER,22]
13 - "BILL".ROWID[ROWID,10], "BILL"."FID"[VARCHAR2,44]
14 - "T_BD_MEASUREUNIT".ROWID[ROWID,10], "T_BD_MEASUREUNIT"."FID"[VARCHAR2,44],
"T_BD_MEASUREUNIT"."FCOEFFICIENT"[NUMBER,22]
15 - "T_BD_MEASUREUNIT".ROWID[ROWID,10], "T_BD_MEASUREUNIT"."FID"[VARCHAR2,44]
-------------------------------------------------------------------------------
[ 本帖最后由 saintfl 于 2010-8-8 20:20 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7652889/viewspace-672196/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7652889/viewspace-672196/