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

?

下面就复盘一下这个案例的整个过程，注：版权归盖总(eygle)所有~

?

问题描述：

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

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分钟。

?

优化过程：

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.

再次执行：