熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle更新操作優化

2013-11-13 15:48:53  來源: Oracle 

  首先描述一下更新的要求根據遠端數據庫中幾張表的關聯結果來刷新本地表中一個字段的值如果本地表中記錄的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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.