Oracle的更新操作优化(1)

出处:　

 

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

首先描述一下更新的要求，根据远端数据库中几张表的关联结果来刷新本地表中一个字段的值。如果本地表中记录的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 已连接。

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 已连接。

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

表已创建。