一卡通數據庫物化視圖優化

本帖最后由 spider0283 于 2011-11-3 17:31 编辑

我們的一卡通庫 1號升級後，一個物化視圖log漲到16.8G，1號凌晨遠端物化視圖刷新時，

因為要刪本機的物化視圖log，而造成ORA-01555錯誤，UNDO段撐到32G。

仔細研究了一下開發寫的代碼，發現還是有改善的空間，以下是開發的代碼。

其中 ecard_hrms_emp_total為員工信息表，有134萬筆記錄， ecard_com_empcard

為卡片資料表，有156萬筆記錄。兩者關聯再去更新ecard_hrms_emp_card_snapshot

表到最新的卡片資料狀態。

而ecard_hrms_emp_card_snapshot上有物化視圖Log，遠端DB有以此基表而建立的物化視圖
每隔6小時會快速刷新遠端的物化視圖。

因為這個存儲過程是只要cardid 流水號匹配，就會update ecard_hrms_emp_card_snapshot

表的資料，因此它對應的物化視圖log的記錄數量與這個基表的記錄數實際是一致的，物化

視圖Log並不能發揮作用。

CREATE OR REPLACE PROCEDURE ECARD_XXXXX_YT.ecard_hrms_emp_card_to_zh as
  errcode varchar2(255);
  errmsg  varchar2(255);
  ------將資料同步到卡片整合資料庫
begin
  merge into ecard_hrms_emp_card_snapshot d
  using (select a.empno          empno,
            b.cardid          cardid,
            b.cardstatusid    cardstatusid,
            b.cardstatuschgday cardstatuschgday,
            b.softno          softno,
            b.cardcode       cardcode
         from ecard_hrms_emp_total a, ecard_com_empcard b
      WHERE a.empid = b.empid) s
  on (d.cardid = s.cardid)
  when matched then
update
   set d.empno          = s.empno,
         d.cardstatusid    = s.cardstatusid,
         d.cardstatuschgday = s.cardstatuschgday,
         d.softno          = s.softno,
         d.cardcode       = s.cardcode
  when not matched then
insert
   (d.empno,
   d.cardid,
   d.cardstatusid,
   d.cardstatuschgday,
   d.softno,
   d.cardcode)
values
   (s.empno,
   s.cardid,
   s.cardstatusid,
   s.cardstatuschgday,
   s.softno,
   s.cardcode);
  ------將釋放的卡片流水號刪除20080912
  begin
delete from ecard_hrms_emp_card_snapshot
   where cardid not in (select cardid from ecard_com_empcard);
  exception
when others then
   null;
  end;
  dbms_standard.commit;
update ecard_cardid_to_zh_flag set flag = 0;
dbms_standard.commit;
exception
  when others then
errcode := sqlcode;
errmsg  := sqlerrm;
insert into ecard_err_log
   (l_type, l_errcode, l_errmsg)
values
   ('ecard_hrms_emp_card_to_zh', errcode, errmsg);
----modified by dragon 20081024 將記錄表的標示標為資料同步失敗 1 代表失敗
update ecard_cardid_to_zh_flag set flag = 1;
dbms_standard.commit;
end;
/
我根據這個存儲過程的邏輯，寫了新的一個物化視圖，並且為這個新的物化視圖建立物

化視圖log

DROP MATERIALIZED VIEW ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1;
CREATE MATERIALIZED VIEW ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1
TABLESPACE PARTION_DATA
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
         INITIAL       64K
         NEXT          1M
         MINEXTENTS    1
         MAXEXTENTS    UNLIMITED
         PCTINCREASE    0
         BUFFER_POOL    DEFAULT
         )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST
START WITH TO_DATE('03-十一月-2011 23:43:57','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/3
WITH PRIMARY KEY
AS
/* Formatted on 2011/11/03 17:22 (Formatter Plus v4.8.8) */
SELECT a.ROWID rowid1, b.ROWID rowid2, a.empno empno, b.cardid cardid,
   b.cardstatusid cardstatusid, b.cardstatuschgday cardstatuschgday,
   b.softno softno, b.cardcode cardcode
  FROM ecard_XXXXX_yt.ecard_hrms_emp_total a,
   ecard_XXXXX_yt.ecard_com_empcard b
WHERE a.empid = b.empid;

COMMENT ON MATERIALIZED VIEW ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1 IS 'snapshot table for snapshot ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1';

DROP SNAPSHOT LOG ON ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1;
CREATE MATERIALIZED VIEW LOG ON ECARD_XXXXX_YT.ECARD_HRMS_EMP_CARD_SNAPSHOT1
TABLESPACE PARTION_INDEX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
         INITIAL       64K
         NEXT          1M
         MINEXTENTS    1
         MAXEXTENTS    UNLIMITED
         PCTINCREASE    0
         BUFFER_POOL    DEFAULT
         )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID
EXCLUDING NEW VALUES;

這樣做到底有什麼好處？

1、原來的 ecard_hrms_emp_card_snapshot表不用被定期merge，以得到最新卡片資料，

只要換去查ecard_hrms_emp_card_snapshot1 物化視圖即可

2、遠端物化視圖刷新時也只需訪問ecard_hrms_emp_card_snapshot1即可，因為這個本

地物化視圖每次被Update的量很少，所以遠端的物化視圖刷新速度也很快

一卡通數據庫 物化視圖優化

一卡通數據庫物化視圖優化