首先描述一下更新的要求根據遠端數據庫中幾張表的關聯結果來刷新本地表中一個字段的值如果本地表中記錄的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> SET TIMING ON
SQL> BEGIN
UPDATE T SET TYPE =
WHERE TYPE =
AND ID IN
(
SELECT TID
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
);
UPDATE T SET TYPE =
WHERE TYPE =
AND NOT EXISTS
(
SELECT
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
AND TID = TID
);
END;
/
PL/SQL 過程已成功完成
已用時間: : :
SQL> ROLLBACK;
回退已完成
已用時間: : :
當然也可以通過一個UPDATE來實現更新只不過邏輯略微復雜了一點
SQL> UPDATE T SET TYPE =
(
SELECT TYPE
FROM
(
SELECT TID DECODE(TID NULL ) TYPE
FROM T
(
SELECT TID
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
) T
WHERE TID = TID(+)
AND TTYPE != DECODE(TID NULL )
) A
WHERE TID = AID
)
WHERE EXISTS
(
SELECT
FROM
(
SELECT TID DECODE(TID NULL ) TYPE
FROM T
(
SELECT TID
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
) T
WHERE TID = TID(+)
AND TTYPE != DECODE(TID NULL )
) A
WHERE TID = AID
)
;
已更新行
已用時間: : :
SQL> ROLLBACK;
回退已完成
已用時間: : :
有的時候一個復雜的SQL並不比兩個簡單的SQL效率要高上面就是一個例子這裡的主要原因是無論是兩次更新還是一個UPDATE語句對遠端的兩個表訪問兩次是無法避免的而一個UPDATE的邏輯更加復雜選擇執行計劃更加困難
由於訪問遠端對象的代價是相對比較大的下面通過PL/SQL的方式來避免對遠端對象的多次訪問
SQL> DECLARE
V_TYPE NUMBER;
BEGIN
FOR I IN (SELECT ID TYPE FROM T) LOOP
SELECT DECODE(COUNT(TID) ) INTO V_TYPE
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
AND TID = IID;
IF ITYPE != V_TYPE THEN
UPDATE T SET TYPE = V_TYPE WHERE ID = IID;
END IF;
END LOOP;
END;
/
PL/SQL 過程已成功完成
已用時間: : :
SQL> ROLLBACK;
回退已完成
已用時間: : :
目前的效率已經基本可以了但是對於數據量比較大的情況這種方式效率仍然比較低雖然對遠端表只讀取一次但是在循環中進行這個操作效率肯定要比直接通過SQL執行低而且對於每個匹配的記錄執行一次UPDATE這也是比較低效的修改PL/SQL代碼通過批量處理的方式來執行
SQL> DECLARE
TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE T_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V_ID T_ID;
V_TYPE T_TYPE;
BEGIN
SELECT TID DECODE(TID NULL ) TYPE
BULK COLLECT INTO V_ID V_TYPE
FROM T
(
SELECT TID
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
) T
WHERE TID = TID(+)
AND TTYPE != DECODE(TID NULL )
;
FORALL I IN V_IDCOUNT
UPDATE T SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
END;
/
PL/SQL 過程已成功完成
已用時間: : :
SQL> ROLLBACK;
回退已完成
已用時間: : :
通過運用PL/SQL減少遠端對象的訪問次數和批量操作的運用整個過程的執行時間已經從原來的多秒優化到了秒如果這時候檢查執行計劃可以發現由於是對本地的更新Oracle選擇當前站點作為驅動站點且對遠端三個表的查詢采用了NESTED LOOP如果使用HINT來規定驅動站點和HASH JOIN連接方式還是獲得一定的性能提升
SQL> DECLARE
TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE T_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V_ID T_ID;
V_TYPE T_TYPE;
BEGIN
SELECT TID DECODE(TID NULL ) TYPE
BULK COLLECT INTO V_ID V_TYPE
FROM T
(
SELECT /*+ DRIVING_SITE(T) USE_HASH(T T) USE_HASH(T) */ TID
FROM T@YTK T T@YTK T T@YTK T
WHERE TID = TID
AND TID = TID
) T
WHERE TID = TID(+)
AND TTYPE != DECODE(TID NULL )
;
FORALL I IN V_IDCOUNT
UPDATE T SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
END;
/
PL/SQL 過程已成功完成
已用時間: : :
SQL> ROLLBACK;
回退已完成
已用時間: : :
從秒提高到秒效果似乎並不明顯不過執行時間已經縮短了%對於大數據量的情況這個%的性能提高會十分客觀
通過這個例子想說明幾個問題
第一Tom所說的能使用一條SQL就用一條SQL完成不能使用SQL的話可以使用PL/SQL完成這句話在大部分的情況下是正確的但是並不意味著SQL一定比PL/SQL快單條SQL一定比兩個SQL快上面的例子很好的說明了這個問題
第二批量操作一般情況下要比PL/SQL循環效率高上面的例子中就通過循環和批量兩種方法對比很好的說明了這個問題但是認為批量操作就一定比循環操作快對於例子中的兩個SQL調用都可以認為是一個批量操作但是由於對遠端表訪問了兩次效率遠遠低於只訪問遠端對象一次的循環操作
第三優化方法是多種多樣的但是優化思路的固定的這個例子中優化的原則無非是盡量減少遠端對象的訪問將單條操作轉化為批量操作盡量減少交互次數幾種
From:http://tw.wingwit.com/Article/program/Oracle/201311/17339.html
