Oracle的更新操作优化(2)

上一篇 / 下一篇 2008-07-15 21:08:16 / 个人分类：Oracle数据库升级

查看( 17 ) / 评论( 0 ) / 评分( 0 / 0 )

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 /