Oracle的更新操作优化(1)(3)

时间:2011-04-12 23:18来源:未知作者:admin 点击: 次

分享到：

已用时间: 00: 00: 10.67 SQL> ROLLBACK; 回退已完成。已用时间: 00: 00: 00.07 目前的效率已经基本可以了，但是对于数据量比较大的情况，这种方式效率仍然比较低

已用时间: 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 /