更新操作中的外关联（二）

今天同事和我说，UPDATE一个表的时候，这个表和其他表进行外关联获取更新数据无效。

简单描述一下UPDATE中为什么无法使用外关联，并对比几种方式的执行计划和性能。

更新操作中的外关联（一）：http://yangtingkun.itpub.net/post/468/462581

上面一篇文章中，给出了更新中需要外关联的几种解决方法，并根据经验对性能进行了判断。不过上一篇文章没有解释为什么UPDATE语句中被更新的表不能和其他表进行外关联，其实答案很简单，Oracle的UPDATE更新执行计划不支持这种写法：

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
^Kn)V8F!W0---------- ---------- ------------------------------ ----------ITPUB个人空间V r5K'h.]
         1         1 A                                       1ITPUB个人空间mN%];v;[I6n Y
         2            B                                       2

SQL> SELECT * FROM T_PRIMARY;

        ID NAME                                  AGEITPUB个人空间0w#Y%V$pW*|&pdl
---------- ------------------------------ ----------ITPUB个人空间^_ k"I8S
q/R(k ZH
        1 C                                      10

SQL> SET AUTOT ONITPUB个人空间YOLb!m']
SQL> UPDATE T_UPDATE AITPUB个人空间-EH#u4A0Uk-v1HT
  2  SET NAME =ITPUB个人空间H
S5]SEl
]7w/x2E
  3   (
w PY6YUt`
|xdZ0  4    SELECT NVL(B.NAME, 'NULL')
a:a(X ]U;U#`0  5    FROM T_PRIMARY BITPUB个人空间_w0\q'{3T3gw
  6    WHERE A.FID = B.ID(+)
#Z&@9`*RGP'{0  7   );

已更新2行。

jl ?8t+h8BT:L._h0执行计划ITPUB个人空间h9s"s_Ee}
QG
----------------------------------------------------------

};{m1qMXhQ9x W0Plan hash value: 1794350833

--------------------------------------------------------------------------------------------
Y? `-A|gr0| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
x8xi#yl"P0--------------------------------------------------------------------------------------------ITPUB个人空间TF5~SuF
|   0 | UPDATE STATEMENT             |              |     2 |    60 |     2   (0)| 00:00:01 |ITPUB个人空间d.G3|+U%M"F!j
|   1 |  UPDATE                      | T_UPDATE     |       |       |            |          |
Y/^a[
xs5x(E0|   2 |   TABLE ACCESS FULL          | T_UPDATE     |     2 |    60 |     2   (0)| 00:00:01 |
fD~;X+\|%Jm]0|   3 |   TABLE ACCESS BY INDEX ROWID| T_PRIMARY    |     1 |    30 |     1   (0)| 00:00:01 |
-L!w+m
tZ2|Kv0|*  4 |    INDEX UNIQUE SCAN         | SYS_C0019519 |     1 |       |     1   (0)| 00:00:01 |ITPUB个人空间3i-B(^,KG+]1V
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间)mpmZ ~V0t%b
---------------------------------------------------

   4 - access("B"."ID"(+)=:B1)

NoteITPUB个人空间![Q!~h9[g
-----
sW e3Z2N0   - dynamic sampling used for this statement

)iGl8|7Y wIyD-@0统计信息ITPUB个人空间7LGbz:c9o)d
----------------------------------------------------------
(RQ'N3@tV;T0          0  recursive calls
C6S}0A*B/u0          3  db block getsITPUB个人空间vOZji8K+i)v
          9  consistent gets
R;`+p0`Mi0          0  physical reads
'v0[N"{4x$f z0          0  redo sizeITPUB个人空间 Y
SI!J%yO+O
        679  bytes sent via SQL*Net to client
0tL7t.Q,N)`6~H%Gi0        645  bytes received via SQL*Net from client
*WC OYL0          4  SQL*Net roundtrips to/from client
!@*_,H;~2}9F+P0          1  sorts (memory)ITPUB个人空间*qW;[R`g:D
          0  sorts (disk)
^-H*k9h7I1[t$`0          2  rows processed

观察执行计划可以发现，两张表是通过UPDATE操作连接在一起的，这种操作类似NESTED LOOP操作，但是并不是NESTED LOOP OUTER连接。

如果从另一个角度分析，所有的外连接都应该可以写成SQL标准的写法，比如LEFT JOIN的方式，而这种在子查询中的连接，显然无法改为这种方式。无论是IN子查询，EXISTS子查询还是UPDATE赋值语句中的子查询都是一样的，Oracle并不会对外连接的(+)给出错误信息，而只是简单的忽略了这个外关联写法。

了解了Oracle无法为子查询中的连接提供外关联特性后，下面来关注一下上面一篇文章给出的几种不同解决方法的性能分析：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE AITPUB个人空间 R2@0n*E@v
  2  SET NAME =
u i~Q \AY_^[0  3   NVL(ITPUB个人空间ew stM n
  4    (
y pmI5u8?t,N"x0  5     SELECT B.NAMEITPUB个人空间2BE P;ZI#s
  6     FROM T_PRIMARY BITPUB个人空间pE#@"J2f!N9cM
  7     WHERE A.FID = B.IDITPUB个人空间8N)y^` J3g[
  8    ), 'NULL'),ITPUB个人空间v"O-G8wQ+j'R&t M
  9   AGE =ITPUB个人空间)C1Kn7j&Fd C5[
 10   NVL(
f)uU
@(V(e]\0 11    (ITPUB个人空间2{U/RF,S:{;n
 12     SELECT B.AGE
oE4D2S8y$TBy0 13     FROM T_PRIMARY B
'L.u~%E7fgV0 14     WHERE A.FID = B.IDITPUB个人空间S/^W$[#L1D1lV
 15    ), 0)
0k k BIU@r0 16   ;

已更新2行。

6Y_u8a1` i0执行计划ITPUB个人空间{8}i(@ ]2m
----------------------------------------------------------
(vhk%FT9_c0Plan hash value: 1792095892

---------------------------------------------------------------------------------------------ITPUB个人空间)C$cO KD W
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
"~ x-B@8I'n\8Ka0---------------------------------------------------------------------------------------------
0`P)zp]uC0|   0 | UPDATE STATEMENT             |              |     2 |    86 |     2   (0)| 00:00:01 |
pz3}5]MZ6vu0|   1 |  UPDATE                      | T_UPDATE     |       |       |            |          |
]_,QRk4@{_0U0|   2 |   TABLE ACCESS FULL          | T_UPDATE     |     2 |    86 |     2   (0)| 00:00:01 |
$qHr$\L'y0|   3 |   TABLE ACCESS BY INDEX ROWID| T_PRIMARY    |     1 |    30 |     1   (0)| 00:00:01 |
q iH+fV"]xAH0|*  4 |    INDEX UNIQUE SCAN         | SYS_C0019519 |     1 |       |     1   (0)| 00:00:01 |ITPUB个人空间eG)_;NJV y
|   5 |   TABLE ACCESS BY INDEX ROWID| T_PRIMARY    |     1 |    26 |     1   (0)| 00:00:01 |
+pk8j/cJ_,|0|*  6 |    INDEX UNIQUE SCAN         | SYS_C0019519 |     1 |       |     1   (0)| 00:00:01 |
T"y)kz%Q2`$P1]"R0---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间;{*C)c
BX:b8\ L
---------------------------------------------------

   4 - access("B"."ID"=:B1)
uZV;F+mnh$v*H`0   6 - access("B"."ID"=:B1)

Note
"x9`!BW O5i_Y
uYa^0-----ITPUB个人空间p0^4s:m$QGh@T
   - dynamic sampling used for this statement

ITPUB个人空间9\&]|
V#Wu
^#Q;IQ/G#cZ
统计信息ITPUB个人空间(`3bcl{+Zl
----------------------------------------------------------
2X_8@l3l$T&] kv0          0  recursive calls
j)oU6b/W:u7So!V {1c}0          3  db block getsITPUB个人空间"|I/V8T{0n&}8X%Y/O T^
         11  consistent gets
S4n.VapM0          0  physical readsITPUB个人空间n;b,]i6b
          0  redo sizeITPUB个人空间 sj#JPi1N5wU0`
        679  bytes sent via SQL*Net to clientITPUB个人空间-xA.F&u2L:_
        737  bytes received via SQL*Net from clientITPUB个人空间JXZ4L{W
          4  SQL*Net roundtrips to/from clientITPUB个人空间!B3S.G5a"}$K%oIn
          1  sorts (memory)
-R
r:Tj9|e0          0  sorts (disk)
f$Nx MT;EN$u0          2  rows processed

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
F+Cy_6b1E1p0  2  SET (NAME, AGE) =ITPUB个人空间 gDt|@(M6@
  3   (
Ctk
M;o0  4    SELECT NVL(B.NAME, 'NULL'), NVL(B.AGE, 0)
;C'Y%B1S6K9R!{/zQZ5]+wG0  5    FROM T_PRIMARY B, T_UPDATE CITPUB个人空间&rn8w3|;F9YP%UB
  6    WHERE C.FID = B.ID(+)
~oV!a#e @#`O3\0  7    AND A.ID = C.ID
_l|&b!T2B5p)Z,~0  8   );

已更新2行。

ITPUB个人空间 H~ppF
执行计划
*\[sT!?9G F;S"d0----------------------------------------------------------
&P+P MSg2MH t.y0Plan hash value: 4124145006

----------------------------------------------------------------------------------------------ITPUB个人空间}F2Q lZ
ZP5s
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
i ` UG"PB @.o*~0----------------------------------------------------------------------------------------------
rb(d.Rch0|   0 | UPDATE STATEMENT              |              |     2 |    86 |     2   (0)| 00:00:01 |ITPUB个人空间:[G^Q}+D
|   1 |  UPDATE                       | T_UPDATE     |       |       |            |          |ITPUB个人空间|O G~D8E?X
|   2 |   TABLE ACCESS FULL           | T_UPDATE     |     2 |    86 |     2   (0)| 00:00:01 |ITPUB个人空间H Azo.~7s
|   3 |   NESTED LOOPS OUTER          |              |     1 |    69 |     2   (0)| 00:00:01 |ITPUB个人空间"UOlq d zv6{"u
|   4 |    TABLE ACCESS BY INDEX ROWID| T_UPDATE     |     1 |    26 |     1   (0)| 00:00:01 |
1aA1E6Y x|Ql0\0|*  5 |     INDEX UNIQUE SCAN         | SYS_C0019518 |     1 |       |     1   (0)| 00:00:01 |
Jy OZ3V3b0|   6 |    TABLE ACCESS BY INDEX ROWID| T_PRIMARY    |   164 |  7052 |     1   (0)| 00:00:01 |
o"u#V W9w`4nV\T a0|*  7 |     INDEX UNIQUE SCAN         | SYS_C0019519 |     1 |       |     0   (0)| 00:00:01 |ITPUB个人空间,FEA5W{mr9m
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间5A1q0}|
|O1G}5V
---------------------------------------------------

   5 - access("C"."ID"=:B1)ITPUB个人空间8lK'_1jMqf
   7 - access("C"."FID"="B"."ID"(+))

Note
beHKc9| f3_H0-----ITPUB个人空间+{+rE FL-\
   - dynamic sampling used for this statement

5{QCX.q,l0统计信息ITPUB个人空间;HT\!vw+g-k%VY8rk
----------------------------------------------------------ITPUB个人空间Z9n3@'sd,~^3Qj
          0  recursive calls
}B)c|N;x0q&B0          3  db block gets
w;]ofbk e.s0         13  consistent gets
$E+Qe!T`qL Iq0          0  physical reads
%x+QWe3`(W?v(~0          0  redo sizeITPUB个人空间 S#@5Z+_C(j#@3}G&o
        679  bytes sent via SQL*Net to client
:J.W-mE?J0        698  bytes received via SQL*Net from client
E|/J"Cp4B
G0          4  SQL*Net roundtrips to/from client
!~Ji`8T-Io0          1  sorts (memory)
JhL1d7O0          0  sorts (disk)
(V?!_v`at*v0          2  rows processed

SQL> ROLLBACK;

回退已完成。

SQL> UPDATEITPUB个人空间8V"tl'y2eANS]
  2  (ITPUB个人空间4j8?"F8g6{h
t7s
  3   SELECT A.NAME A_NAME,ITPUB个人空间5U&FW;b
u
  4    A.AGE A_AGE,
-wH.FWc2q4I#MX0  5    B.NAME B_NAME,ITPUB个人空间8l4D9a(WU]6C,J
  6    B.AGE B_AGEITPUB个人空间E$d+Q/N/R#[
  7   FROM T_UPDATE A, T_PRIMARY B
P$Aqa;z }'Z S0  8   WHERE A.FID = B.ID(+)
mJhPD5]`0  9  )ITPUB个人空间AD*Q6Y:QlEs
 10  SET A_NAME = NVL(B_NAME, 'NULL'),ITPUB个人空间_+}+XV/b.~-NM1l!s
 11   A_AGE = NVL(B_AGE, 0);

已更新2行。

ITPUB个人空间(b4`}6V5vOOd/tn
执行计划
%e5P)^?w[K(M3x0----------------------------------------------------------ITPUB个人空间t0so+mk\*} ^
Plan hash value: 322399923

----------------------------------------------------------------------------------------------
T)Bb;AQy"B
m3CEv |0| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间:c8D ZX;A p9Ei6d6^
----------------------------------------------------------------------------------------------ITPUB个人空间!D+T+HS;Y
E9j3D
|   0 | UPDATE STATEMENT              |              |     2 |   172 |     3   (0)| 00:00:01 |
S"jI%K#G0z0|   1 |  UPDATE                       | T_UPDATE     |       |       |            |          |ITPUB个人空间HaLAN Lo
|   2 |   NESTED LOOPS OUTER          |              |     2 |   172 |     3   (0)| 00:00:01 |ITPUB个人空间6U&U5tl5V
|   3 |    TABLE ACCESS FULL          | T_UPDATE     |     2 |    86 |     2   (0)| 00:00:01 |
+ZmKQl `M0|   4 |    TABLE ACCESS BY INDEX ROWID| T_PRIMARY    |     1 |    43 |     1   (0)| 00:00:01 |
]5a7w3s)P+B|0BV.A:~0|*  5 |     INDEX UNIQUE SCAN         | SYS_C0019519 |     1 |       |     0   (0)| 00:00:01 |
P6^2Zy b|d6i0----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
2o,\-h1cC6})i ]V|0---------------------------------------------------

   5 - access("A"."FID"="B"."ID"(+))

Note
)oq PN\.DZ@0-----ITPUB个人空间-N ]&?;[ BP2b
R
   - dynamic sampling used for this statement

(|l6qe+]_0统计信息ITPUB个人空间)mF*k nlzc
----------------------------------------------------------
YuZ[h|0          0  recursive calls
kmn|u0S-Bhu8x0          3  db block getsITPUB个人空间E%P6Sd:dp]
          9  consistent getsITPUB个人空间Yk9^4u3p&w`b
          0  physical readsITPUB个人空间/r:z%|B-jnv
          0  redo size
hFT9h0@'oi.MIX2i0        679  bytes sent via SQL*Net to client
O0D6Em)B#R0        729  bytes received via SQL*Net from clientITPUB个人空间T
Bs2g"C)GM,pA u
          4  SQL*Net roundtrips to/from client
$@l2IQ/qm0          1  sorts (memory)
b4I-E'ri9E2S qI7J#y0          0  sorts (disk)ITPUB个人空间5Y'o_;JNVA
          2  rows processed

显然最后一种方法由于少关联一张表而效率最高，而对于两个字段的情况，似乎第一种方法和第二种方法性能相差不多，而第一种似乎还好一点，但是可以预测的是，一旦需要更新的字段增加，第二种方法的性能就会远远好于第一种。