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)
时间: 2014-08-14

MySQL笔记之子查询使用介绍

子查询是将一个查询语句嵌套在另一个查询语句中 内层查询语句的查询结果,可以为外层查询语句提供查询条件 因为在特定情况下,一个查询语句的条件需要另一个查询语句来获取 参考表:employee 参考表:department 带IN关键字的子查询 复制代码 代码如下: mysql> SELECT * FROM employee    -> WHERE d_id IN    -> (SELECT d_id FROM department);+------+------+--------+----

利用带关联子查询Update语句更新数据的方法

Update是T-sql中再简单不过的语句了,update table set column=expression [where condition],我们都会用到.但update的用法不仅于此,真正在开发的时候,灵活恰当地使用update可以达到事半功倍的效果. 假定有表Table1(a,b,c)和Table2(a,c),现在Table1中有些记录字段c为null,要根据字段a在Table2中查找,取出字段a相等的字段c的值来更新Table1.一种常规的思路,通过游标遍历Table1中字段c为

MySQL子查询的几种常见形式介绍

mysql子查询的几种常见写法: 复制代码 代码如下: select * from xxx where col = [any|all](select * from xxxx); 该句法可分为加关键词和不加关键词的写法,当不加关键词的时候,子查询语句返回的是一个离散值(注意是一个),查询语句将以子查询语句的结果作为自己 where子句的条件进行查询,该句法可以在子查询语句前加入any.all.some等关键字,此时子查询语句返回的是一组离散值.any则表示,查询语句是以子查询返回的值作为一个范围,

MySQL查询优化:用子查询代替非主键连接查询实例介绍

一对多的两张表,一般是一张表的外键关联到另一个表的主键.但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联. 例如: 复制代码 代码如下: create table t_team ( tid int primary key, tname varchar(100) ); create table t_people ( pid int primary key, pname varchar(100), team_name varchar(100) ); team表和people表是一对多的关

Oracle基础学习之子查询

首先使用子查询的时候注意事项包括,子查询可以嵌套多层和子查询需要圆括号()括起来,下面我们来看看详细的介绍. 基础介绍 1,wherer:子查询一般会返回单行单列 单行多列 多行单列 : 2,having:子查询会返回单行单列,同时表示要使用统计函数: 3,from:子查询返回多行多列数据(表结构): 4,select:返回单行单列 (一般不使用): 示例详解 where(进行数据行的筛选操作): a:查询出低于公司平均工资的雇员信息. select * from emp where sal<(

详细讲述MySQL中的子查询操作

继续做以下的前期准备工作: 新建一个测试数据库TestDB: create database TestDB; 创建测试表table1和table2: CREATE TABLE table1 ( customer_id VARCHAR(10) NOT NULL, city VARCHAR(10) NOT NULL, PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE table2 ( order_id

MySQL利用profile分析慢sql详解(group left join效率高于子查询)

使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况.分析器可以更好的展示出不良 SQL 的性能问题所在. 最近遇到一个查询比较慢的sql语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化.单独查询单表或者子查询记录都很快,下面来看看详细的介绍. 开启profile mysql> show profiles; -- 查看是否开启 Empty set, 1 warning (0.00

使用SQL Server数据库嵌套子查询的方法

很多SQL Server程序员对子查询(subqueries)的使用感到困惑,尤其对于嵌套子查询(即子查询中包含一个子查询).现在,就让我们追本溯源地探究这个问题.  有两种子查询类型:标准和相关.标准子查询执行一次,结果反馈给父查询.相关子查询每行执行一次,由父查询找回.在本文中,我将重点讨论嵌套子查询(nested subqueries)(我将在以后介绍相关子查询).  试想这个问题:你想生成一个卖平垫圈的销售人员列表.你需要的数据分散在四个表格中:人员.联系方式(Person.Contac

SQL编程之子查询及注意事项

当一个查询是另一个查询的条件时,称之为子查询.子查 询可以使用几个简单命令构造功能强大的复合命令.子查询最常用于SELECT-SQL命令的 WHERE子句中.子查询是一个 SELECT 语句,它嵌套在一个 SELECT.SELECT...INTO 语句.INSERT...INTO 语句.DELETE 语句.或 UPDATE 语句或嵌套在另一子查询中. 语法:select ....from 表1 where 列1 > (子查询) 外面的查询成为父查询,圆括号嵌入的查询成为称为子查询.SQL Ser