Oracle的更新操作优化(3)

出处:　

 

已用时间: 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,  T2,  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,  T2,  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调用，都可以认为是一个批量操作，但是由于对远端表访问了两次，效率远远低于只访问远端对象一次的循环操作。

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