查看: 2307|回复: 8

一条SQL的优化

[复制链接]
认证徽章
论坛徽章:
15
数据库板块每日发贴之星
日期:2006-10-22 01:01:542014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:42:472011新春纪念徽章
日期:2011-01-04 10:24:02ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21生肖徽章2007版:羊
日期:2009-09-10 11:27:42生肖徽章2007版:羊
日期:2009-03-10 21:16:04
发表于 2010-8-8 19:58 | 显示全部楼层 |阅读模式
怎么修改比较好
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 编辑 ]
论坛徽章:
27
数据库板块每日发贴之星
日期:2010-06-17 01:01:07迷宫蛋
日期:2011-07-07 15:25:46紫蛋头
日期:2011-08-10 10:31:56ITPUB十周年纪念徽章
日期:2011-09-27 16:33:28ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222012新春纪念徽章
日期:2012-02-07 09:59:35ITPUB知识分享者
日期:2012-02-20 17:49:25铁扇公主
日期:2012-02-21 15:02:40ITPUB年度最佳BLOG写作奖
日期:2012-03-13 17:09:53ITPUB 11周年纪念徽章
日期:2012-10-09 18:14:48
发表于 2010-8-8 20:12 | 显示全部楼层
lz 把sql 语句格式化一下,方便阅读。。

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-8 20:22 | 显示全部楼层
从执行计划上看应该不太慢啊,执行具体的情况呢

set autot trace执行看看

使用道具 举报

回复
认证徽章
论坛徽章:
15
数据库板块每日发贴之星
日期:2006-10-22 01:01:542014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:42:472011新春纪念徽章
日期:2011-01-04 10:24:02ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21生肖徽章2007版:羊
日期:2009-09-10 11:27:42生肖徽章2007版:羊
日期:2009-03-10 21:16:04
发表于 2010-8-8 20:31 | 显示全部楼层
实际这个是个功能性问题,主要是这个SQL造成了表的锁,由于现在找不到开发人员去修改应用。所以就只能考虑如何去尽量减少执行的时间。

[ 本帖最后由 saintfl 于 2010-8-8 20:40 编辑 ]

使用道具 举报

回复
认证徽章
论坛徽章:
15
数据库板块每日发贴之星
日期:2006-10-22 01:01:542014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:42:472011新春纪念徽章
日期:2011-01-04 10:24:02ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21生肖徽章2007版:羊
日期:2009-09-10 11:27:42生肖徽章2007版:羊
日期:2009-03-10 21:16:04
发表于 2010-8-8 20:36 | 显示全部楼层
1 row updated.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
----------------------

| Id  | Operation                        | Name                         | Rows
| Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------
----------------------

|   0 | UPDATE STATEMENT                 |                              |     1
|    56 |     1   (0)|

|   1 |  UPDATE                          | T_SD_JMLPRICEPOLICYMATERIALS |
|       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID    | T_SD_JMLPRICEPOLICYMATERIALS |     1
|    56 |     1   (0)|

|   3 |    INDEX UNIQUE SCAN             | PK_JMLPRICEPOLICYM           |     1
|       |     1   (0)|

|   4 |   SORT GROUP BY NOSORT           |                              |     1
|    95 |   140   (1)|

|   5 |    NESTED LOOPS                  |                              |     7
|   665 |     5   (0)|

|   6 |     TABLE ACCESS BY INDEX ROWID  | T_SD_SALEORDERENTRY          |   134
|  8844 |     4   (0)|

|   7 |      INDEX RANGE SCAN            | JML_POLICYMATERIALID_ON_SALE |    66
|       |     1   (0)|

|   8 |     INDEX UNIQUE SCAN            | PK_SALEORDER_FID             |     1
|    29 |     1   (0)|

|   9 |      NESTED LOOPS                |                              |     7
|   630 |     2   (0)|

|  10 |       TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILLENTRY          |     7
|   406 |     1   (0)|

|  11 |        INDEX RANGE SCAN          | FCOREBILLID                  |     7
|       |     1   (0)|

|  12 |       TABLE ACCESS BY INDEX ROWID| T_AR_OTHERBILL               |     1
|    32 |     1   (0)|

|  13 |        INDEX UNIQUE SCAN         | PK_AR_OTHERBILL              |     1
|       |     1   (0)|

|  14 |   TABLE ACCESS BY INDEX ROWID    | T_BD_MEASUREUNIT             |     1
|    32 |     1   (0)|

|  15 |    INDEX UNIQUE SCAN             | PK_MEASUREUNIT               |     1
|       |     1   (0)|

--------------------------------------------------------------------------------
----------------------


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          3  db block gets
       3901  consistent gets
          0  physical reads
          0  redo size
        822  bytes sent via SQL*Net to client
       1505  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-8 20:47 | 显示全部楼层
你应该去查查为什么阻塞这个语句的会话一直不提交

使用道具 举报

回复
认证徽章
论坛徽章:
15
数据库板块每日发贴之星
日期:2006-10-22 01:01:542014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:42:472011新春纪念徽章
日期:2011-01-04 10:24:02ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21生肖徽章2007版:羊
日期:2009-09-10 11:27:42生肖徽章2007版:羊
日期:2009-03-10 21:16:04
发表于 2010-8-8 21:00 | 显示全部楼层
原帖由 棉花糖ONE 于 2010-8-8 20:47 发表
你应该去查查为什么阻塞这个语句的会话一直不提交


多谢!这个在单据上是由于单据的回写操作。关于这方面有什么好的建议吗?

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-8 21:26 | 显示全部楼层

回复 #7 saintfl 的帖子

1. 你这个更新的是pk,单据回写怎么会2个人同时写到一条单据上呢,程序问题的可能性比较大,单从业务本身来说你应该去看看是不是有些表不必要关联,我大概看了下

2.t_sd_saleorder SO, t_sd_saleorderentry SE,这个语句里的t_sd_saleorder SO表应该没必要使用,
so.fid = se.fparentid ,这个条件应该程序插入的时候就应该保证,表体行的id肯定是来自用表头的id


3. 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)))))

这部分检查下t_ar_otherbillentry  是否有一个单据行状态的字段,一般单据上有个分录状态,单据表体分录单据上也会有个单据状态,如果有的话就没必要关联t_ar_otherbill  表

4.语句中的group by是没必要的,sum中的case when也没必要

使用道具 举报

回复
认证徽章
论坛徽章:
15
数据库板块每日发贴之星
日期:2006-10-22 01:01:542014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:42:472011新春纪念徽章
日期:2011-01-04 10:24:02ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21生肖徽章2007版:羊
日期:2009-09-10 11:27:42生肖徽章2007版:羊
日期:2009-03-10 21:16:04
发表于 2010-8-8 22:19 | 显示全部楼层
多谢!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表