首先描述一下更新的要求根据远端数据库中几张表的关联结果来刷新本地表中一个字段的值如果本地表中记录的ID在远端表关联中可以查询的到则这条记录的相应字段更新为否则如果对应记录在远端无法查询到记录则这个字段更新为
这个需求比较简单但是被更新表是物化视图复制的基表需要将修改复制到多个远端物化视图中因此为了避免将过多不必要的修改传播到远端站点这里有一个额外的要求只更新当前状态不正确的记录也就是说更新之前要判断更新前和更新后是否一样只有二者不一样才需要更新
最后一点要求是不建立临时表使用SQL或者PL/SQL来尽量高效的实现这个功能不使用临时表的要求是处于两点考虑一是由于需求本身很简单写SQL或PL/SQL最多也就十几行而已为这么简单的需求建立一个临时表没有什么必要;另外一点是由于当前数据库版本为INSERT INTO SELECT插入临时表存在bug产生的REDO比插入普通表还要高详细情况可以参考临时表产生REDO过多的bug
下面还是通过例子来详细说明
SQL> CONN YANGTK/YANGTK@YTK已连接
SQL> CREATE TABLE T AS SELECT ROWNUM ID A* FROM DBA_OBJECTS A;
表已创建
SQL> ALTER TABLE T ADD PRIMARY KEY (ID);
表已更改
SQL> CREATE TABLE T AS SELECT ROWNUM ID B* FROM DBA_SYNONYMS B;
表已创建
SQL> CREATE INDEX IND_T_ID ON T(ID);
索引已创建
SQL> ALTER TABLE T MODIFY ID NOT NULL;
表已更改
SQL> CREATE TABLE T AS SELECT ROWNUM ID COWNER CTABLE_NAME CCOLUMN_NAME
FROM DBA_TAB_COLUMNS C;
表已创建
SQL> ALTER TABLE T ADD PRIMARY KEY (ID);
表已更改
SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T)
PL/SQL 过程已成功完成
SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T)
PL/SQL 过程已成功完成
SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T)
PL/SQL 过程已成功完成
SQL> CONN YANGTK/YANGTK@YTK已连接
SQL> CREATE TABLE T AS SELECT ROWNUM ID OBJECT_NAME MOD(ROWNUM ) TYPE FROM DBA_OBJECTS A;
表已创建
SQL> ALTER TABLE T ADD PRIMARY KEY (ID);
表已更改
SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T)
PL/SQL 过程已成功完成
SQL> CREATE DATABASE LINK YTK CONNECT TO YANGTK IDENTIFIED BY YANGTK USING YTK;
数据库链接已创建
在这个例子中需要更新YTK数据库中T表的TYPE字段如果T表中一条记录的ID可以在远端TTT表的联合查询中查询到则这条记录的TYPE应该更新为如果查询不到对应的记录则需要更新TYPE的值为O如果当前的TYPE的值已经满足要求则不需要进行更新
最简单的方法莫过于更新两次每次只更新一部分数据
SQL>SETTIMINGON
SQL>BEGIN
UPDATETSETTYPE=
WHERETYPE=
ANDIDIN
(
SELECTTID
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
);
UPDATETSETTYPE=
WHERETYPE=
ANDNOTEXISTS
(
SELECT
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
ANDTID=TID
);
END;
/
PL/SQL 过程已成功完成
已用时间: : :
SQL> ROLLBACK;
回退已完成
已用时间: : :
当然也可以通过一个UPDATE来实现更新只不过逻辑略微复杂了一点
SQL>UPDATETSETTYPE=
(
SELECTTYPE
FROM
(
SELECTTIDDECODE(TIDNULL)TYPE
FROMT
(
SELECTTID
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
)T
WHERETID=TID(+)
ANDTTYPE!=DECODE(TIDNULL)
)A
WHERETID=AID
)
WHEREEXISTS
(
SELECT
FROM
(
SELECTTIDDECODE(TIDNULL)TYPE
FROMT
(
SELECTTID
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
)T
WHERETID=TID(+)
ANDTTYPE!=DECODE(TIDNULL)
)A
WHERETID=AID
)
;
已更新行
已用时间: : :
SQL> ROLLBACK;
回退已完成
已用时间: : :
有的时候一个复杂的SQL并不比两个简单的SQL效率要高上面就是一个例子这里的主要原因是无论是两次更新还是一个UPDATE语句对远端的两个表访问两次是无法避免的而一个UPDATE的逻辑更加复杂选择执行计划更加困难
由于访问远端对象的代价是相对比较大的下面通过PL/SQL的方式来避免对远端对象的多次访问
SQL>DECLARE
V_TYPENUMBER;
BEGIN
FORIIN(SELECTIDTYPEFROMT)LOOP
SELECTDECODE(COUNT(TID))INTOV_TYPE
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
ANDTID=IID;
IFITYPE!=V_TYPETHEN
UPDATETSETTYPE=V_TYPEWHEREID=IID;
ENDIF;
ENDLOOP;
END;
/
PL/SQL 过程已成功完成
已用时间: : :
SQL> ROLLBACK;
回退已完成
已用时间: : :
目前的效率已经基本可以了但是对于数据量比较大的情况这种方式效率仍然比较低虽然对远端表只读取一次但是在循环中进行这个操作效率肯定要比直接通过SQL执行低而且对于每个匹配的记录执行一次UPDATE这也是比较低效的修改PL/SQL代码通过批量处理的方式来执行
SQL>DECLARE
TYPET_IDISTABLEOFNUMBERINDEXBYBINARY_INTEGER;
TYPET_TYPEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;
V_IDT_ID;
V_TYPET_TYPE;
BEGIN
SELECTTIDDECODE(TIDNULL)TYPE
BULKCOLLECTINTOV_IDV_TYPE
FROMT
(
SELECTTID
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
)T
WHERETID=TID(+)
ANDTTYPE!=DECODE(TIDNULL)
;
FORALLIINV_IDCOUNT
UPDATETSETTYPE=V_TYPE(I)WHEREID=V_ID(I);
END;
/
PL/SQL 过程已成功完成
已用时间: : :
SQL> ROLLBACK;
回退已完成
已用时间: : :
通过运用PL/SQL减少远端对象的访问次数和批量操作的运用整个过程的执行时间已经从原来的多秒优化到了秒如果这时候检查执行计划可以发现由于是对本地的更新Oracle选择当前站点作为驱动站点且对远端三个表的查询采用了NESTED LOOP如果使用HINT来规定驱动站点和HASH JOIN连接方式还是获得一定的性能提升
SQL>DECLARE
TYPET_IDISTABLEOFNUMBERINDEXBYBINARY_INTEGER;
TYPET_TYPEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;
V_IDT_ID;
V_TYPET_TYPE;
BEGIN
SELECTTIDDECODE(TIDNULL)TYPE
BULKCOLLECTINTOV_IDV_TYPE
FROMT
(
SELECT/*+DRIVING_SITE(T)USE_HASH(TT)USE_HASH(T)*/TID
FROMT@YTKTT@YTKTT@YTKT
WHERETID=TID
ANDTID=TID
)T
WHERETID=TID(+)
ANDTTYPE!=DECODE(TIDNULL)
;
FORALLIINV_IDCOUNT
UPDATETSETTYPE=V_TYPE(I)WHEREID=V_ID(I);
END;
/
PL/SQL 过程已成功完成
已用时间: : :
SQL> ROLLBACK;
回退已完成
已用时间: : :
从秒提高到秒效果似乎并不明显不过执行时间已经缩短了%对于大数据量的情况这个%的性能提高会十分客观
通过这个例子想说明几个问题
第一Tom所说的能使用一条SQL就用一条SQL完成不能使用SQL的话可以使用PL/SQL完成这句话在大部分的情况下是正确的但是并不意味着SQL一定比PL/SQL快单条SQL一定比两个SQL快上面的例子很好的说明了这个问题
第二批量操作一般情况下要比PL/SQL循环效率高上面的例子中就通过循环和批量两种方法对比很好的说明了这个问题但是认为批量操作就一定比循环操作快对于例子中的两个SQL调用都可以认为是一个批量操作但是由于对远端表访问了两次效率远远低于只访问远端对象一次的循环操作
第三优化方法是多种多样的但是优化思路的固定的这个例子中优化的原则无非是尽量减少远端对象的访问将单条操作转化为批量操作尽量减少交互次数几种
