update子查询使用介绍
基础知识
1,关联子查询和非关联子查询
在非关联子查询中,内部查询只执行一次并返回它的值给外部查询,然后外部查询在它的处理中使用内部查询返回给它的值。而在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并返回它的记录。然后,外部查询根据返回的记录做出决策。
如:
SELECTo1.CustomerID,o1.OrderID,o1.OrderDate FROMOrderso1 WHEREo1.OrderDate=(SELECTMax(OrderDate) FROMOrderso2 WHEREo2.CustomerID=o1.CustomerID)
是一个关联子查询
SELECTo1.CustomerID,o1.OrderID,o1.OrderDate FROMOrderso1 WHEREo1.OrderDateIN (SELECTTOP2o2.OrderDate FROMOrderso2 WHEREo2.CustomerID=o1.CustomerID) ORDERBYCustomerID
是一个非关联子查询
2,提示(HINT)
一般在优化时,无论采用基于规则的或是基于代价的方法,由Oracle系统的优化器来决定语句的执行路径。这样的选择的路径不要见得是最好的。所以,Oracle提供了一种方法叫提示的方法。它可以让编程人员按照自己的要求来选择执行路径,即提示优化器该按照什么样的执行规则来执行当前的语句。这样可以在性能上比起Oracle优化自主决定要好些。
通常情况下,编程人员可以利用提示来进行优化决策。通过运用提示可以对下面内容进行指定:
lSQL语句的优化方法;
l对于某条SQL语句,基于开销优化程序的目标;
lSQL语句访问的访问路径;
l连接语句的连接次序;
l连接语句中的连接操作。
如果希望优化器按照编程人员的要求执行,则要在语句中给出提示。提示的有效范围有限制,即有提示的语句块才能按照提示要求执行。下面语句可以指定提示:
l简单的SELECT,UPDATE,DELETE语句;
l复合的主语句或子查询语句;
l组成查询(UNION)的一部分。
提示的指定有原来的注释语句在加“+”构成。语法如下:
[SELECT|DELETE|UPDATE]/*+[hint|text]*/
或
[SELECT|DELETE|UPDATE]--+[hint|text]
注意在“/*”后不要空就直接加“+”,同样“--+”也是连着写。
警告:如果该提示语句书写不正确,则Oracle就忽略掉该语句。
常见的提示有:
Ordered强制按照from子句中指定的表的顺序进行连接
Use_NL强制指定两个表间的连接方式为嵌套循环(NestedLoops)
Use_Hash强制指定两个表间的连接方式为哈希连接(HashJoin)
Use_Merge强制指定两个表间的连接方式为合并排序连接(MergeJoin)
Push_Subq让非关联子查询提前执行
Index强制使用某个索引
3,执行计划
在PL/SQLDeveloper的SQLWINDOWS中用鼠标或键盘选中SQL语句,然后按F5,就会出现执行计划解析的界面:
4,Update的特点
Update的系统内部执行情况可以参照附文:对update事务的内部分析.doc
使用Update的基本要点就是,
1)尽量使用更新表上的索引,减少不必要的更新
2)更新的数据来源花费时间尽可能短,如果无法做到就把更新内容插入到中间表中,然后给中间表建上索引,再来更新
3)如果更新的是主键,建议删除再插入。
5,示例用表
后面的阐述将围绕以下两张表展开:
Createtabletab1(workdatevarchar2(8),cinovarchar2(15),val1number,val2number);
Createtabletab2(workdatevarchar2(8),cinovarchar2(15),val1number,val2number);
Createtabletab3(workdatevarchar2(8),cinovarchar2(15),val1number,val2number);
Createtabletab4(workdatevarchar2(8),cinovarchar2(15),val1number,val2number);
workdate,cino为两张表的关键字,默认情况没有建主键索引。
二,Update两种情况
用Update更新某个表,无外乎是两种情况:根据关联子查询,更新字段;通过非关联子查询,限定更新范围。如果还有第三种情况,那就是前两种情况的叠加。
1,根据关联子查询,更新字段
Updatetab1t Set(val1,val2)=(selectval1,val2 fromtab2 whereworkdate=t.workdate andcino=t.cino);
通过tab2来更新tab1的相应字段。执行SQL语句时,系统会从tab1中一行一行读记录,然后再通过关联子查询,找到相应的字段来更新。关联子查询能否通过tab1的条件快速的查找到对应记录,是优化能否实现的必要条件。所以一般都要求在tab2上建有Unique或者排重性较高的Normal索引。执行所用时间大概为(查询tab1中一条记录所用的时间+在tab2中查询一条记录所用的时间)*tab1中的记录条数。
如果子查询条件比较复杂,如以下语句:
Updatetab1t Set(val1,val2)=(selectval1,val2 fromtab2tt whereexists(select1 fromtab3 whereworkdate=tt.workdate andcino=tt.cino) andworkdate=t.workdate andcino=t.cino);
这时更新tab1中的每条记录花费在子查询上的时间将成倍增加,如果tab1中的记录数较多,这种更新语句几乎是不可完成。
解决方式是,把子查询提取出来,做到中间表中,然后给中间表建上索引,用中间表来代替子查询,这样速度就能大大提高:
Insertintotab4 selectworkdate,cino,val1,val2 fromtab2tt whereexists(select1 fromtab3 whereworkdate=tt.workdate andcino=tt.cino); createindextab4_ind01ontab4(workdate,cino); Updatetab1t Set(val1,val2)=(selectval1,val2 fromtab4tt whereworkdate=t.workdate andcino=t.cino);
2,通过非关联子查询,限定更新范围
Updatetab1t setval1=1 where(workdate,cino)in(selectworkdate,cinofromtab2)
根据tab2提供的数据范围,来更新tab1中的相应记录的val1字段。
在这种情况下,系统默认执行方式往往是先执行selectworkdate,cinofromtab2子查询,在系统中形成系统视图,然后在tab1中选取一条记录,查询系统视图中是否存在相应的workdate,cino组合,如果存在,则更新tab1,如果不存在,则选取下一条记录。这种方式的查询时间大致等于:子查询查询时间+(在tab1中选取一条记录的时间+在系统视图中全表扫描寻找一条记录时间)*tab1的记录条数。其中“在系统视图中全表扫描寻找一条记录时间”会根据tab2的大小而有所不同。若tab2记录数较小,系统可以直接把表读到系统区中;若tab2记录数多,系统无法形成系统视图,这时会每一次更新动作,就把子查询做一次,速度会非常的慢。
针对这种情况的优化有两种
1)在tab1上的workdate,cino字段上加入索引,同时增加提示。
修改以后的SQL语句如下:
Update/*+ordereduse_nl(sys,t)*/tab1t setval1=1 where(workdate,cino)in(selectworkdate,cinofromtab2)
其中sys表示系统视图。如果不加入ordered提示,系统将会默认以tab1表作为驱动表,这时就要对tab1作全表扫描。加入提示后,使用系统视图,即selectworkdate,cinofromtab2,作为驱动表,在正常情况下,速度能提高很多。
2)在tab2表上的workdate,cino字段加入索引,同时改写SQL语句:
Updatetab1t setval1=1 whereexists(select1 fromtab2 whereworkdate=t.workdate andcino=t.cino)
三,索引问题
update索引的使用比较特殊,有时看起来能用全索引,但实际上却只用到一部分,所以建议把复合索引的各字段写在一起。
例如:
Update/*+ordereduse_nl(sys,t)*/tab1t setval1=1 wherecinoin(selectcinofromtab2) andworkdate="200506"
这条SQL语句是不能完全用到tab1上的复合索引workdate+cino的。能用到的只是workdate="200506"的约束。
如果写成这样,就没问题:
Update/*+ordereduse_nl(sys,t)*/tab1t setval1=1 where(workdate,cino)in(selectworkdate,cinofromtab2)
相关文章
- Postman使用教程图解
- 更好的 Aria2 容器化使用方案
- 【实用的开源项目】使用服务器部署memos,一款拥有社交功能的、好看的自托管备忘录
- 教你在国内如何使用ChatGPT
- Mysql联合查询UNION和UNION ALL的使用介绍详解数据库
- 函数VL2 函数在Oracle中的应用使用Oracle中的NVL2函数优化SQL查询(oracle的nvl2)
- 数据如何使用MongoDB快速查询所有数据(mongodb查询所有)
- Oracle 视图 V$DNFS_CHANNELS 官方解释,作用,如何使用详细说明
- MySQL JOIN使用技巧:轻松搞定两表关联查询(mysqljoin用法)
- Oracle 数据库分级查询的使用方法介绍(分级查询oracle)
- 使用Linux查询进程名称的简单步骤(linux查询进程名称)
- 如何使用Linux查询和安装软件包(linux查询安装包)
- 入门必看:VPS Linux的使用技巧(vpslinux怎么用)
- 使用MSSQL查询本周数据的技巧(mssql 查询本周数据)
- MySQL中使用offset实现分页查询(mysql中offser)
- MySQL使用IN查询时数量超限问题的解决方法(mysql中in超过数量)
- 实现高效登录系统使用Redis缓存技术(登录系统使用redis)
- 避免使用MySQL中的IN操作,提升查询效率(mysql 不使用in)
- 简易指南使用Redis客户端连接服务器(如何redis客户端)
- Oracle中使用父子值定义的实现方法(oracle中父子值定义)
- Access使用查询--1.1.用选择查询建立计算字段
- 关于SQLServer查询语句的使用
- 如何使用索引提高查询速度
- js中array的sort()方法使用介绍
- Mysql联合查询UNION和UNIONALL的使用介绍
- 在WCF数据访问中使用缓存提高Winform字段中文显示速度的方法