动手为王：由一条UPDATE语句引发的一波三折深入实践(含PPT)

关注我们获得更多精彩

编辑说明：这篇文章源自我几年前的一个演讲，当时我在『甲骨文技术大会』分享了一个关于SQL的主题，其中的一个案例推演了几个步骤，后来本文作者『刘晨』做了验证、实验，并且分享在他的博客上，今天我们整理了这篇文章和大家分享，第一个原因是，虽然我在大会做过分享，但是从未详细的写过这个案例；第二个原因是，我们特别赞赏这样的动手实验实证能力，正是这样的能力才会让别人的经验成为我们的知识和技能，不断成长。（盖国强）

（获取这个演讲的PPT，请关注本公众号回复：YHEM 获取）

问题背景

盖总在之前的甲骨文大会的演讲中，通过一个简单的 UPDATE 语句，为我们展示了什么叫由点及面的优化，什么叫由点及面的知识覆盖度，不在于这个案具体如何操作，更应关注或更值得我们借鉴的是这种学习态度和方法思路....我想这个案例可以带给我们一些启迪。

下面就复盘一下这个案例的整个过程

问题的标题是：“并行更新成为系统瓶颈”

SQL：

UPDATE /*+ parallel(a, 8) */ tbl_a a
SET name = (SELECT name FROM tbl_b WHERE id = a.id),
class = (SELECT class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);

现象是这条 SQL 执行时间非常长，从介绍看是有 2.5 分钟（原SQL执行长达数小时）。

1. 为了以下可以更清楚地说明问题，对这个 SQL 做了简化处理，我们需要优化的是这条 SQL：

UPDATE tbl_a a
SET name = (SELECT name FROM tbl_b WHERE id = a.id),
class = (SELECT class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT id FROM tbl_b b);

我们创建两张模拟表：

SQL> create table tbl_a(
id number,
name varchar2(5),
class varchar2(5));
Table created.

SQL> create table tbl_b(
id number,
name varchar2(5),
class varchar2(5));
Table created.

SQL> create sequence seq_a cache 1000;
Sequence created.

SQL> create sequence seq_b cache 1000;
Sequence created.

插入一些随机数据：

begin
for i in 1 .. 100000 loop
insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from tbl_a;
COUNT(*)
------------
100000

begin
for i in 1 .. 10000 loop
insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from tbl_b;
COUNT(*)
------------
10000

2. 执行原SQL语句

SQL> set timing on
SQL> UPDATE tbl_a a
SET name = (SELECT name FROM tbl_b WHERE id = a.id),
class = (SELECT class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT id FROM tbl_b b);
10000 rows updated.

Elapsed: 00:00:07.42

需要 7 秒多的时间(虽然和示例中2.5分钟有差距，但仅为了说明优化的问题，时间上的差距可以忽略)。

3. 第一次优化

我们从这个SQL中可以看到，更新 TBL_A 表的 ID 列，但TBL_B表的SELECT有三次，即三次的全表扫描，那么要是能减少TBL_B表检索的次数，执行时间肯定可以减少。

SQL> UPDATE tbl_a a
SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT id FROM tbl_b b);
10000 rows updated.

Elapsed: 00:00:04.04

这样的调整是符合SQL语法的，执行时间变为了4秒多，效果显著。

4. 第二次优化

虽然执行时间减少了接近一半，但 SQL 中还是对 TBL_B 执行了两次扫描，是否还可以减少一次？

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SET name = b_name, class = b_class;
SET name = b_name, class = b_class
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Elapsed: 00:00:00.01

这样就做到了只扫描一次 TBL_B 表，直接对子查询更新，但此时报了一个错误，ORA-01779，

这就引出了 non key-preserved table 的概念。非键值保存表，杨长老的博客(http://blog.itpub.net/4227/viewspace-195889/)中提到过这个错误：

“造成这个错误的原因是更新的列不是事实表的列，而是维度表的列。换句话说，如果两张表关联，其中一张表的关联列是主键，那么另一张表就是事实表，也就是说另一张表中的列就是可更新的；除非另一张表的关联列也是主键，否则这张表就是不可更新的，如果更新语句涉及到了这张表，就会出现 ORA-1799 错误。如果是两张表主键关联，那么无论更新那个表的字段都可以。

其实这个限制的真正原因是 Oracle 要确保连接后更新的内容可以写到一张表中，而这就要求连接方式必须是 1 对 N 或者 1 对 1 的连接。这样才能确保连接后的结果集数量和事实表一致。从而使得 Oracle 对连接后子查询的更新可以顺利的更新到事实表中。”

a.id=b.id，我们是用 TBL_B 的 id 列作为条件更新，需要确保这列只会对应到 TBL_B表的一行记录，可以为表 TBL_B 的 id 列设置主键、唯一索引或唯一约束，三种操作，这里选择设置唯一约束：

SQL> alter table tbl_b add constraint uq_b_id unique(id);
Table altered.

再次执行：

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SET name = b_name, class = b_class;
10000 rows updated.

Elapsed: 00:00:00.12

执行时间一下仅为 0.12 秒。

上面如果 TBL_A 的 ID 列设置为主键，则为 1 对 1 的连接，如果仅是 TBL_B 的 ID 列为唯一约束，则为 1 对 N 的连接。

回顾一下，演讲中 PPT 的示范，主要在于这一页：

通过两次优化，执行时间从 7 秒降到了 0.12 秒，虽然这里的示例数据未必和实际情况一致，但成比例的缩放足以说明这个问题，从这个案例可以看出，优化的本质就是少做事，原始 SQL 执行三次全表扫描，那目标就是减少全表扫描的次数，第一次优化的操作可能相对容易想到，但第二次优化的操作，就需要知道可以有这种语法，而且出现了 ORA-01799 的错误，还需要知道这种错误的根本原因是什么，才能有可行的解决方法。

问题还没完，以上说明了 SQL 语句的优化，下面就是针对这条 SQL 展开的知识。

假设上面的 TBL_A 和 TBL_B 表是属于用户 bisal 的，此时新建一个用户 phibisal，并授予最简单的权限：

SQL> create user phibisal identified by phibisal;
User created.

SQL> grant create session to phibisal;
Grant succeeded.

bisal 用户创建这两张表的 public 同义词：

SQL> create public synonym tbl_a for bisal.tbl_a;
Synonym created.

SQL> create public synonym tbl_b for bisal.tbl_b;
Synonym created.

然后授予 phibisal 用户对 TBL_A 表的读和更新权限：

SQL> grant select, update on tbl_a to phibisal;
Grant succeeded.

此时 phibisal 登录后执行：

sqlplus phibisal/phibisal

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SET name = b_name, class = b_class;
FROM tbl_a a, tbl_b b
*
ERROR at line 2:
ORA-00942: table or view does not exist

会提示 TBL_B 不存在，因为用户没有该表的任何权限，(注：此处和 eygle 的示例中反馈不同，他提示的是 ORA-01031: insufficient privileges)
如果授予 phibisal 对 TBL_B 表的读权限，

SQL> grant select on tbl_b to phibisal;
Grant succeeded.

此时可以完成更新：

sqlplus phibisal/phibisal

SQL> UPDATE tbl_a a
SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT id FROM tbl_b b);
10000 rows updated.

但用如下 SQL 会提示权限错误：

UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SET name = b_name, class = b_class;
FROM tbl_a a, tbl_b b
*
ERROR at line 2:
ORA-01031: insufficient privileges

即这种子查询更新会因没有 TBL_B 表的 UPDATE 权限报错。
但如果使用如下 with 语法，则可以正常执行：

SQL> UPDATE
(WITH tmp AS (
SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
)
SET name = b_name, class = b_class;
10000 rows updated.

做得更彻底一些：

SQL> revoke update on tbl_a from phibisal;
Revoke succeeded.

撤消了 phibisal 用户对 TBL_A 的更新权限，按理说，phibisal 用户不应该能再更新 TBL_A 表了。

使用上面两个调整后的 SQL，确实如此：

sqlplus phibisal/phibisal

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SET name = b_name, class = b_class;
FROM tbl_a a, tbl_b b
*
ERROR at line 2:
ORA-01031: insufficient privileges

SQL> UPDATE tbl_a a
SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
WHERE a.id IN (SELECT id FROM tbl_b b);
UPDATE tbl_a a
*
ERROR at line 1:
ORA-01031: insufficient privileges

但是，奇怪的是如下 SQL 可以执行：

SQL> UPDATE
(WITH tmp AS (
SELECT b.name b_name, b.class b_class, a.name, a.class
FROM tbl_a a, tbl_b b
WHERE a.id = b.id)
SELECT * FROM tmp
)
SET name = b_name, class = b_class;
10000 rows updated.

这就从原理规则上，违背了权限控制，看下版本：

SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

这就是2014年7月提出的一个 bug，在11.2.0.3、11.2.0.4、12.1等版本中都存在的一个问题，需要修正这个bug，相当于使用 with 语法，可以绕过用户权限，对没有权限的表进行 DML 操作。

精髓不在于这个 bug，而是在于从一条简单的 UPDATE 语句，可以派生出如此丰富的知识，可谓举一反三，受益匪浅。一方面需要我们能够从原理上理解每一个概念，另一方面也要培养自己举一反三，知识点由点及面的想法，做到真正的触类旁通。

作者：刘晨

转载自：bisal的个人杂货铺

投稿：有投稿、寻求报道意向技术人请在公众号对话框留言。

更多精彩请关注 “数据和云” 公众号