Oracle的更新操作优化(1)

首先描述一下更新的要求，根据远端数据库中几张表的关联结果来刷新本地表中一个字段的值。如果本地表中记录的ID在远端表关联中可以查询的到，则这条记录的相应字段更新为1，否则如果对应记录在远端无法查询到记录，则这个字段更新为0。

最后一点要求是不建立临时表，使用SQL或者PL/SQL来尽量高效的实现这个功能。不使用临时表的要求是处于两点考虑，一是由于需求本身很简单，写SQL或PL/SQL最多也就十几行而已，为这么简单的需求建立一个临时表没有什么必要;另外一点是由于当前数据库版本为9204，INSERT INTO SELECT插入临时表存在bug，产生的REDO比插入普通表还要高，详细情况可以参考：临时表产生REDO过多的bug：http://yangtingkun.itpub.net/post/468/450680

下面还是通过例子来详细说明：

SQL> CONN YANGTK/YANGTK@YTK102已连接。

SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

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

表已更改。

SQL> CREATE TABLE T2 AS SELECT ROWNUM ID, B.* FROM DBA_SYNONYMS B;

表已创建。

SQL> CREATE INDEX IND_T2_ID ON T2(ID);

索引已创建。

SQL> ALTER TABLE T2 MODIFY ID NOT NULL;

表已更改。

SQL> CREATE TABLE T3 AS SELECT ROWNUM ID, C.OWNER, C.TABLE_NAME, C.COLUMN_NAME

2 FROM DBA_TAB_COLUMNS C;

表已创建。

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

表已更改。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

SQL> CONN YANGTK/YANGTK@YTK92已连接。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, OBJECT_NAME, MOD(ROWNUM, 2) TYPE FROM DBA_OBJECTS A;

表已创建。

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 ;