Oracle的更新操作优化

来源：中国IT实验室作者：佚名出处：巧巧读书 2008-03-06 进入讨论组

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> CREATE DATABASE LINK YTK102 CONNECT TO YANGTK IDENTIFIED BY YANGTK USING 'YTK102';

数据库链接已创建。

在这个例子中，需要更新YTK102数据库中T表的TYPE字段，如果T表中一条记录的ID可以在远端T1、T2、T3表的联合查询中查询到，则这条记录的TYPE应该更新为1，如果查询不到对应的记录，则需要更新TYPE的值为O，如果当前的TYPE的值已经满足要求，则不需要进行更新。

最简单的方法莫过于更新两次，每次只更新一部分数据：

PL/SQL 过程已成功完成。
已用时间: 00: 00: 44.28

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 01.10

当然，也可以通过一个UPDATE来实现更新，只不过逻辑略微复杂了一点：

SQL> UPDATE T SET TYPE = 
2 ( 
3 SELECT TYPE 
4 FROM 
5 ( 
6 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE 
7 FROM T, 
8 ( 
9 SELECT T1.ID 
10 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
11 WHERE T1.ID = T2.ID 
12 AND T2.ID = T3.ID 
13 ) T1 
14 WHERE T.ID = T1.ID(+) 
15 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1) 
16 ) A 
17 WHERE T.ID = A.ID 
18 ) 
19 WHERE EXISTS 
20 ( 
21 SELECT 1 
22 FROM 
23 ( 
24 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE 
25 FROM T, 
26 ( 
27 SELECT T1.ID 
28 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
29 WHERE T1.ID = T2.ID 
30 AND T2.ID = T3.ID 
31 ) T1 
32 WHERE T.ID = T1.ID(+) 
33 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1) 
34 ) A 
35 WHERE T.ID = A.ID 
36 ) 
37 ;

已更新15407行。
已用时间: 00: 01: 18.03

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 00.15

有的时候，一个复杂的SQL并不比两个简单的SQL效率要高，上面就是一个例子。这里的主要原因是，无论是两次更新，还是一个UPDATE语句，对远端的两个表访问两次是无法避免的，而一个UPDATE的逻辑更加复杂，选择执行计划更加困难。

由于访问远端对象的代价是相对比较大的，下面通过PL/SQL的方式来避免对远端对象的多次访问：

SQL> DECLARE 
2 V_TYPE NUMBER; 
3 BEGIN 
4 FOR I IN (SELECT ID, TYPE FROM T) LOOP 
5 SELECT DECODE(COUNT(T1.ID), 0, 0, 1) INTO V_TYPE 
6 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
7 WHERE T1.ID = T2.ID 
8 AND T2.ID = T3.ID 
9 AND T1.ID = I.ID; 
10 
11 IF I.TYPE != V_TYPE THEN 
12 UPDATE T SET TYPE = V_TYPE WHERE ID = I.ID; 
13 END IF; 
14 END LOOP; 
15 END; 
16 /