update子查询使用介绍

update⼦查询使⽤介绍
基础知识
1，关联⼦查询和⾮关联⼦查询
在⾮关联⼦查询中，内部查询只执⾏⼀次并返回它的值给外部查询，然后外部查询在它的处理中使⽤内部查询返回给它的值。

⽽在关联⼦查询中，对于外部查询返回的每⼀⾏数据，内部查询都要执⾏⼀次。

另外，在关联⼦查询中是信息流是双向的。

外部查询的每⾏数据传递⼀个值给⼦查询，然后⼦查询为每⼀⾏数据执⾏⼀次并返回它的记录。

然后，外部查询根据返回的记录做出决策。

如：
SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate = (SELECT Max(OrderDate)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)
是⼀个关联⼦查询
SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate IN
(SELECT TOP 2 o2.OrderDate
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID
是⼀个⾮关联⼦查询
2，提⽰（HINT）
⼀般在优化时，⽆论采⽤基于规则的或是基于代价的⽅法，由Oracle系统的优化器来决定语句的执⾏路径。

这样的选择的路径不要见得是最好的。

所以，Oracle提供了⼀种⽅法叫提⽰的⽅法。

它可以让编程⼈员按照⾃⼰的要求来选择执⾏路径，即提⽰优化器该按照什么样的执⾏规则来执⾏当前的语句。

这样可以在性能上⽐起Oracle优化⾃主决定要好些。

通常情况下，编程⼈员可以利⽤提⽰来进⾏优化决策。

通过运⽤提⽰可以对下⾯内容进⾏指定：
l SQL语句的优化⽅法；
l 对于某条SQL语句，基于开销优化程序的⽬标；
l SQL语句访问的访问路径；
l 连接语句的连接次序；
l 连接语句中的连接操作。

如果希望优化器按照编程⼈员的要求执⾏，则要在语句中给出提⽰。

提⽰的有效范围有限制，即有提⽰的语句块才能按照提⽰要求执⾏。

下⾯语句可以指定提⽰：
l 简单的SELECT ,UPDATE ,DELETE 语句；
l 复合的主语句或⼦查询语句；
l 组成查询（UNION）的⼀部分。

提⽰的指定有原来的注释语句在加“+”构成。

语法如下：
[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */
或
[ SELECT | DELETE|UPDATE ] --+ [hint | text ]
注意在“/*”后不要空就直接加“+”，同样 “--+”也是连着写。

警告：如果该提⽰语句书写不正确，则Oracle就忽略掉该语句。

常见的提⽰有：
Ordered 强制按照from⼦句中指定的表的顺序进⾏连接
Use_NL 强制指定两个表间的连接⽅式为嵌套循环（Nested Loops）
Use_Hash 强制指定两个表间的连接⽅式为哈希连接（Hash Join）
Use_Merge 强制指定两个表间的连接⽅式为合并排序连接（Merge Join）
Push_Subq 让⾮关联⼦查询提前执⾏
Index 强制使⽤某个索引
3，执⾏计划
在PL/SQL Developer的SQL WINDOWS中⽤⿏标或键盘选中SQL语句，然后按F5，就会出现执⾏计划解析的界⾯：
4， Update的特点
Update的系统内部执⾏情况可以参照附⽂：对update事务的内部分析.doc
使⽤Update的基本要点就是，
1）尽量使⽤更新表上的索引，减少不必要的更新
2）更新的数据来源花费时间尽可能短，如果⽆法做到就把更新内容插⼊到中间表中，然后给中间表建上索引，再来更新
3）如果更新的是主键，建议删除再插⼊。

5，⽰例⽤表
后⾯的阐述将围绕以下两张表展开：
Create table tab1 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab2 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab3 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab4 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
workdate, cino为两张表的关键字，默认情况没有建主键索引。

⼆，Update两种情况
⽤Update更新某个表，⽆外乎是两种情况：根据关联⼦查询，更新字段；通过⾮关联⼦查询，限定更新范围。

如果还有第三种情况，那就是前两种情况的叠加。

1，根据关联⼦查询，更新字段
Update tab1 t
Set (val1, val2) = (select val1, val2
from tab2
where workdate = t.workdate
and cino = t.cino);
通过tab2来更新tab1的相应字段。

执⾏SQL语句时，系统会从tab1中⼀⾏⼀⾏读记录，然后再通过关联⼦查询，找到相应的字段来更新。

关联⼦查询能否通过tab1的条件快速的查找到对应记录，是优化能否实现的必要条件。

所以⼀般都要求在tab2上建有Unique或者排重性较⾼的Normal索引。

执⾏所⽤时间⼤概为（查询tab1中⼀条记录所⽤的时间 + 在tab2中查询⼀条记录所⽤的时间）* tab1中的记录条数。

如果⼦查询条件⽐较复杂，如以下语句：
Update tab1 t
Set (val1, val2) = (select val1, val2
from tab2 tt
where exists (select 1
from tab3
where workdate = tt.workdate
and cino = tt.cino)
and workdate = t.workdate
and cino = t.cino);
这时更新tab1中的每条记录花费在⼦查询上的时间将成倍增加，如果tab1中的记录数较多，这种更新语句⼏乎是不可完成。

解决⽅式是，把⼦查询提取出来，做到中间表中，然后给中间表建上索引，⽤中间表来代替⼦查询，这样速度就能⼤⼤提⾼：
Insert into tab4
select workdate, cino, val1, val2
from tab2 tt
where exists (select 1
from tab3
where workdate = tt.workdate
and cino = tt.cino);
create index tab4_ind01 on tab4(workdate, cino);
Update tab1 t
Set (val1, val2) = (select val1, val2
from tab4 tt
where workdate = t.workdate
and cino = t.cino);
2，通过⾮关联⼦查询，限定更新范围
Update tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)
根据tab2提供的数据范围，来更新tab1中的相应记录的val1字段。

在这种情况下，系统默认执⾏⽅式往往是先执⾏select workdate, cino from tab2⼦查询，在系统中形成系统视图，然后在tab1中选取⼀条记录，查询系统视图中是否存在相应的workdate, cino组合，如果存在，则更新tab1，如果不存在，则选取下⼀条记录。

这种⽅式的查询时间⼤致等于：⼦查询查询时间 + （在tab1中选取⼀条记录的时间 + 在系统视图中全表扫描寻找⼀条记录时间）* tab1的记录条数。

其中“在系统视图中全表扫描寻找⼀条记录时间”会根据tab2的⼤⼩⽽有所不同。

若tab2记录数较⼩，系统可以直接把表读到系统区中；若tab2记录数多，系统⽆法形成系统视图，这时会每⼀次更新动作，就把⼦查询做⼀次，速度会⾮常的慢。

针对这种情况的优化有两种
1）在tab1上的workdate, cino字段上加⼊索引，同时增加提⽰。

修改以后的SQL语句如下：
Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)
其中sys表⽰系统视图。

如果不加⼊ordered提⽰，系统将会默认以tab1表作为驱动表，这时就要对tab1作全表扫描。

加⼊提⽰后，使⽤系统视图，即select workdate, cino from tab2，作为驱动表，在正常情况下，速度能提⾼很多。

2）在tab2表上的workdate, cino字段加⼊索引，同时改写SQL语句：
Update tab1 t
set val1 = 1
where exists (select 1
from tab2
where workdate = t.workdate
and cino = t.cino)
三，索引问题
update索引的使⽤⽐较特殊，有时看起来能⽤全索引，但实际上却只⽤到⼀部分，所以建议把复合索引的各字段写在⼀起。

例如：
Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where cino in (select cino from tab2)
and workdate = '200506'
这条SQL语句是不能完全⽤到tab1上的复合索引workdate + cino的。

能⽤到的只是workdate='200506'的约束。

如果写成这样，就没问题：
Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)。