查看: 4029|回复: 10

[Tips] UPDATE语句优化一例

[复制链接]
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-5-25 10:48 | 显示全部楼层 |阅读模式
以下SQL:
UPDATE test10 a
SET a.name = (SELECT b.name FROM test11 b WHERE b.id = a.id)
WHERE EXISTS ( SELECT 1 FROM test11 b WHERE a.id = b.id)
由于TEST11中只有部分TEST10的值,如果不写WHERE EXISTS子句,会将TEST11中不存在的值UPDATE成为NULL。但以上写法会导致B表两次扫描(虽然是UNIQUE SCAN)。其执行计划如下:
Execution Plan
----------------------------------------------------------
0    UPDATE STATEMENT Optimizer=CHOOSE
1    0    UPDATE OF 'TEST10'
2    1    FILTER
3    2      TABLE ACCESS (FULL) OF 'TEST10'
4    2      INDEX (UNIQUE SCAN) OF 'TEST_2' (UNIQUE)
5    1    TABLE ACCESS (BY INDEX ROWID) OF 'TEST11'
6    5      INDEX (UNIQUE SCAN) OF 'TEST_2' (UNIQUE)
在oracle的update语句语法中,除了可以update表之外,也可以是视图,所以以上SQL可以改成:
UPDATE (SELECT a.name name_old,b.name name_new FROM test10 a, test11 b WHERE a.id = b.id)
SET name_old = name_new;
注意:这样能避免对B表或其索引的2次扫描,但前提是B表的id字段必需有unique index或primary key。否则报错:
ORA-01779: cannot modify a column which maps to a non key-preserved table
其执行计划如下,很明显避免了两次B表的扫描:
Execution Plan
----------------------------------------------------------
0    UPDATE STATEMENT Optimizer=CHOOSE
1    0    UPDATE OF 'TEST10'
2    1    NESTED LOOPS
3    2        TABLE ACCESS (FULL) OF 'TEST10'
4    2        TABLE ACCESS (BY INDEX ROWID) OF 'TEST11'
5    4            INDEX (UNIQUE SCAN) OF 'TEST_2' (UNIQUE)
论坛徽章:
51
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22铁扇公主
日期:2012-02-21 15:03:13最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-05-22 15:10:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:13:332013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36itpub13周年纪念徽章
日期:2014-09-28 10:55:55
发表于 2011-5-25 11:32 | 显示全部楼层
感谢分享

使用道具 举报

回复
论坛徽章:
0
发表于 2011-5-25 11:37 | 显示全部楼层
更新数据多的话,试试hash join一类的话可能会有惊喜。

使用道具 举报

回复
论坛徽章:
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
发表于 2011-5-25 11:40 | 显示全部楼层
update join view

使用道具 举报

回复
认证徽章
论坛徽章:
27
ITPUB元老
日期:2008-01-15 09:32:23授权会员
日期:2008-08-13 23:37:22ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47迷宫蛋
日期:2012-02-25 10:02:36秀才
日期:2017-03-20 13:42:20
发表于 2011-5-25 11:40 | 显示全部楼层
棉花糖难得出现

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2011-5-25 11:58 | 显示全部楼层
update inline view,还有delete,insert
要求有preserved key,还有其他的要求,比如不能有group by什么的

也可以用merge的,update比inline view的要求少,11g的bypass_ujvc这个hint已经不能用了

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2011-5-25 18:41 | 显示全部楼层
俩版主回复的都不错

学习

使用道具 举报

回复
论坛徽章:
2
2011新春纪念徽章
日期:2011-02-18 11:43:34ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2012-11-30 11:04 | 显示全部楼层
这种完全可以考虑使用merge 做更新,

使用道具 举报

回复
论坛徽章:
24
萤石
日期:2013-01-03 16:13:11海蓝宝石
日期:2013-04-23 17:06:35红宝石
日期:2013-05-17 17:32:35SQL大赛参与纪念
日期:2013-12-06 14:03:45马上有对象
日期:2014-02-18 16:44:08马上有车
日期:2014-02-27 15:22:45优秀写手
日期:2014-03-22 06:00:12马上有房
日期:2014-03-26 19:40:00巨蟹座
日期:2015-10-18 17:42:41
发表于 2012-11-30 11:15 | 显示全部楼层
这个用法 第一次见   学习了

使用道具 举报

回复
论坛徽章:
0
发表于 2012-11-30 12:25 | 显示全部楼层
不说了,学习呗

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表