Oracle更新操作优化

Oracle更新操作优化

 

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

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

　　SQL> SET TIMING ON
　　SQL> BEGIN
　　2 UPDATE T SET TYPE = 1
　　3 WHERE TYPE = 0
　　4 AND ID IN
　　5 (
　　6 SELECT T1.ID
　　7 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
　　8 WHERE T1.ID = T2.ID
　　9 AND T2.ID = T3.ID
　　10 );
　　11
　　12 UPDATE T SET TYPE = 0
　　13 WHERE TYPE = 1
　　14 AND NOT EXISTS
　　15 (
　　16 SELECT 1
　　17 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
　　18 WHERE T1.ID = T2.ID
　　19 AND T2.ID = T3.ID
　　20 AND T.ID = T1.ID
　　21 );
　　22 END;
　　23 /

　　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 /

　　PL/SQL 过程已成功完成。

　　已用时间: 00: 00: 10.67

　　SQL> ROLLBACK;

　　回退已完成。

　　已用时间: 00: 00: 00.07

　　目前的效率已经基本可以了，但是对于数据量比较大的情况，这种方式效率仍然比较低，虽然对远端表只读取一次，但是在循环中进行这个操作效率肯定要比直接通过SQL执行低，而且对于每个匹配的记录执行一次UPDATE，这也是比较低效的，修改PL/SQL代码，通过批量处理的方式来执行：

SQL> DECLARE
　　2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
　　3 TYPE T_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
　　4 V_ID T_ID;
　　5 V_TYPE T_TYPE;
　　6 BEGIN
　　7
　　8 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
　　9 BULK COLLECT INTO V_ID, V_TYPE
　　10 FROM T,
　　11 (
　　12 SELECT T1.ID
　　13 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
　　14 WHERE T1.ID = T2.ID
　　15 AND T2.ID = T3.ID
　　16 ) T1
　　17 WHERE T.ID = T1.ID(+)
　　18 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1)
　　19 ;
　　20
　　21 FORALL I IN 1..V_ID.COUNT
　　22 UPDATE T SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
　　23
　　24 END;
　　25 /

　　PL/SQL 过程已成功完成。

　　已用时间: 00: 00: 00.35

　　SQL> ROLLBACK;

　　回退已完成。

　　已用时间: 00: 00: 00.12

　　通过运用PL/SQL减少远端对象的访问次数和批量操作的运用，整个过程的执行时间已经从原来的50多秒优化到了0.35秒，如果这时候检查执行计划可以发现，由于是对本地的更新，Oracle选择当前站点作为驱动站点，且对远端三个表的查询采用了NESTED LOOP，如果使用HINT来规定驱动站点和HASH JOIN连接方式，还是获得一定的性能提升：

　　SQL> DECLARE
　　2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
　　3 TYPE T_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
　　4 V_ID T_ID;
　　5 V_TYPE T_TYPE;
　　6 BEGIN
　　7
　　8 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
　　9 BULK COLLECT INTO V_ID, V_TYPE
　　10 FROM T,
　　11 (
　　12 SELECT /*+ DRIVING_SITE(T1) USE_HASH(T1 T2) USE_HASH(T3) */ T1.ID
　　13 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
　　14 WHERE T1.ID = T2.ID
　　15 AND T2.ID = T3.ID
　　16 ) T1
　　17 WHERE T.ID = T1.ID(+)
　　18 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1)
　　19 ;
　　20
　　21 FORALL I IN 1..V_ID.COUNT
　　22 UPDATE T SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
　　23
　　24 END;
　　25 /

　　PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.31

　　SQL> ROLLBACK;

　　回退已完成。

　　已用时间: 00: 00: 01.12

　　从0.35秒提高到0.31秒，效果似乎并不明显，不过执行时间已经缩短了10%，对于大数据量的情况，这个10%的性能提高会十分客观。

　　通过这个例子想说明几个问题：

　　第一：Tom所说的能使用一条SQL就用一条SQL完成，不能使用SQL的话，可以使用PL/SQL完成。这句话在大部分的情况下是正确的，但是并不意味着SQL一定比PL/SQL快，单条SQL一定比两个SQL快，上面的例子很好的说明了这个问题。

　　第二：批量操作一般情况下要比PL/SQL循环效率高，上面的例子中就通过循环和批量两种方法对比很好的说明了这个问题。但是认为批量操作就一定比循环操作快。对于例子中的两个SQL调用，都可以认为是一个批量操作，但是由于对远端表访问了两次，效率远远低于只访问远端对象一次的循环操作。

　　第三：优化方法是多种多样的，但是优化思路的固定的。这个例子中优化的原则无非是尽量减少远端对象的访问，将单条操作转化为批量操作，尽量减少交互次数几种。