如何使用update 子查询

共计 4333 个字符，预计需要花费 11 分钟才能阅读完成。

本篇文章给大家分享的是有关如何使用 update 子查询，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。

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)

以上就是如何使用 update 子查询，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。