分析总结：关于两个表关联进行更新

http://www.oracle.com.cn/viewthread.php?tid=112596&highlight=%2Bwait4me
关于两个表关联进行更新，在论坛上经常有人问这个问题。尤其是不少刚刚从sql server转到oracle的
朋友，会把两种数据库的语法混淆。今天正好做了个测试，纪录下来
OS: RedHat Linus AS4
DB: Oracle 10gR2
案例：经典的detp/emp表，现在希望把emp.ename更新成ename+loc的形式。其中loc是通过关联emp和dept
取得的数据。

Last login: Mon Jan 22 15:40:57 2007 from 192.168.10.100
[oracle@testsrv1 ~]$ sqlplus /nolog
[uniread] Loaded history (3738 lines)

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 22 16:19:30 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

idle> conn test/test
Connected.

准备数据的sql如下
复制内容到剪贴板
代码:
test@DW> !cat /tmp/pre.sql
drop table emp;
drop table dept;
CREATE TABLE dept (
      deptno        NUMBER(4),
      dname         VARCHAR2(14),
      loc           VARCHAR2(13));

CREATE TABLE emp (
      empno        NUMBER(4) ,
      ename        VARCHAR2(100),
      job          VARCHAR2(9),
      mgr          NUMBER(4),
      sal          NUMBER(7,2),
      comm         NUMBER(7,2),
      deptno       NUMBER(2)
      );

insert into dept  select * from scott.dept;

insert into emp select empno,ename,job,mgr,sal,comm,deptno  from scott.emp;

commit;

exec dbms_stats.gather_table_stats(user,'DEPT',cascade=>true);
exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
原始数据如下
test@DW> select * from emp;

     EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902        800                    20
      7499 ALLEN      SALESMAN        7698       1600        300         30
      7521 WARD       SALESMAN        7698       1250        500         30
      7566 JONES      MANAGER         7839       2975                    20
      7654 MARTIN     SALESMAN        7698       1250       1400         30
      7698 BLAKE      MANAGER         7839       2850                    30
      7782 CLARK      MANAGER         7839       2450                    10
      7788 SCOTT      ANALYST         7566       3000                    20
      7839 KING       PRESIDENT                  5000                    10
      7844 TURNER     SALESMAN        7698       1500          0         30
      7876 ADAMS      CLERK           7788       1100                    20
      7900 JAMES      CLERK           7698        950                    30
      7902 FORD       ANALYST         7566       3000                    20
      7934 MILLER     CLERK           7782       1300                    10

14 rows selected.

test@DW> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

使用下面的语法（语法1）达到目的（注意，该语法有潜在的错误）
test@DW> update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.deptno=d.deptno);

test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

test@DW> rollback;

Rollback complete.

为什么刚才说语法1有潜在的错误？因为在上面的例子中，两个表的关联部分没有unique属性，那么就可能出现一个
emp.deptno对应多个dept.deptno的情况。这里仅仅是可能出现，因为当这个情况出现的时候，大多是因为原始数据
中存在重复的，多余的数据。

那么，为了简单的避免上面的问题，我们使用语法2，加上一个max函数达到唯一的目的
test@DW> update emp e set e.ename=e.ename ||'.'||(select max(d.loc) from dept d where e.deptno=d.dep
tno);

14 rows updated.

test@DW> rollback;
test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

test@DW> rollback;

Rollback complete.

在重复数据比较多的情况下，我们不想使用了聚合函数max，那么可以使用语法3，加入rownum=1条件
test@DW> update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.deptno=d.deptno a
nd rownum=1);

14 rows updated.

test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

语法2和语法3有什么区别？我们来看看他们的执行计划

先看语法2
test@DW> explain plan for update emp e set e.ename=e.ename ||'.'||(select max(d.loc) from dept d whe
re e.deptno=d.deptno);

Explained.

test@DW> @/Oracle/product/10.2.1/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 1667745622

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    14 |   126 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  4 |    TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("D"."DEPTNO"=:B1)

16 rows selected.

test@DW> rollback;

再来看看语法3
test@DW> explain plan for update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.
deptno=d.deptno and rownum=1);

Explained.

test@DW> @/Oracle/product/10.2.1/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 764578432

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    14 |   126 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   COUNT STOPKEY     |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM=1)
   4 - filter("D"."DEPTNO"=:B1)

17 rows selected.

仔细对比2和3的执行计划，他们唯一的区别在于：2使用了sort aggregate，而3使用了count stopkey

如果我们熟悉oracle分页算法，就知道count stopkey虽然名义上是全表扫描，但是在重复数据多的时候，会
根据指定的rownum=n在得到第n条纪录后停止扫描。而sort aggregate取得max，一定是执行全表扫描的。

通过这个细微的差别，我们可以判断出：当重复数据相对多的时候，使用rownum的语法3比语法2效率高（具体
高多少，这个需要实际测试）


===============================================================================
上面的讨论都是针对普通的表进行的，既没有任何约束条件的表关联。在实际操作中，我们更多情况面对的是
有主外键关系的表。那么，对我们的需求，又有了不同的解决方案

我们先给dept加上一个主键(实际上仅仅用唯一性约束就可以了)
test@DW> alter table dept add constraint pk_dept primary key (deptno);

或者直接使用唯一索引
test@DW> create unique index idx_dept on dept(deptno);

Table altered.

在现在的条件下，我们可以对视图(online view)直接进行更新操作。我们叫他语法4
test@DW> update (select ename,loc from emp,dept where emp.deptno=dept.deptno) v set v.ename=v.ename||'.'||v.loc;

14 rows updated.

在语法4中，我们先建立了一个online view，然后对她进行更新。一个包含多表的视图，是否可以更新，哪些字段
可以更新，涉及到一个叫 kep-preserved table的概念。简单的说，如果一个表的所有key同时也是视图的key，那么
这个表就是kep-preserved table。当视图中仅有一个kpt的时候，可以对kpt的数据进行更新操作。关于key-preseved
table和upatable columns的具体信息，请参考手册。

在我们的例子中，emp是key-preserved table，所以我们可以更新它的字段。
因为唯一索引，所以语法4的执行计划选择了使用索引，避免全表扫描
Execution Plan
----------------------------------------------------------
Plan hash value: 1686319074

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |         |    14 |   280 |     4   (0)| 00:00:01 |
|   1 |  UPDATE                       | EMP     |       |       |            |          |
|   2 |   NESTED LOOPS                |         |    14 |   280 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


总结：根据上面的分析，我个人给出的建议是：
        1。如果满足updatable view的条件，优先使用语法4，更新onlien view
        2。如果没有必要的约束条件，优先使用rownum


如果出现null之类的问题，请确保两张表之间的数据不存在a中有而b没有的