oracle物化視圖心得(轉)

業務描述: 外圍系統需要訪問ERP系統中的數據(兩個數據庫),可用dblink將兩個數據庫聯接,訪問方式有兩種:

    1. 在目的數據庫建立訪問源數據庫的synonym

       優點: 及時性好,只要erp中數據更改后,在目的數據庫立即就可以反映出來

       缺點: 通過dblink直接訪問速度較慢,有時無法使用ebs數據庫中索引或是一些奇怪的問題

    2. 在目的數據庫建立訪問源數據庫的物化視圖

       優點: 訪問速度快,可以對物化視圖加索引,以加快查詢速度

       缺點: 數據及時性不好,如果服務器配置較高此缺點可以忽略

物化視圖建立:

    1. 語法: CREATE MATERIALIZED VIEW viewname

            [refresh [fast/complete/force]
            [on demand/commit]
            [start with date]

            [next date]
            [with {primary key/rowid}]] AS subquery

    2. 語法說明:

       refresh fast: 增量更新,需要在源數據表中建立日志表

       refersh complete: 全表更新,如果數據天中數據量較少可以采用此方式建立物化視圖

       refersh force: 如果增量更新可能則進行增量更新,不可用則進行全表更新.

       注: 如果不指定 refersh 則默認為 refersh force

       on demand: 按照指定的時間頻率進行更新,較常用的一種方法

       on commit: 源數據表中更新數據commit后就刷新物化視圖,此方法及時性較好,但對服務器配置、性能要求相對較高,很少采用

       start with date: 此物化視圖在何時開始第一次刷新

       next date: 指定物化視圖刷新的頻率

       with primary key: 如果源數據表中有單個欄位的 primary key,可以用此方式建立物化視圖

       with rowid: 如果源數據表中沒有單個欄位的primary key,則需使用此方式建立物化視圖

    3. 創建源數據表的日志表:

       CREATE MATERIALIZED VIEW LOG ON owner.tablename;

       注: 在建立此log時,一定要使用和數據表相同的owner,建立好后可在 all_mview_logs view 中找到創建的相關信息

     4. 實例:

        a.創建物料信息的物化視圖(增量更新)

        因為物料信息表中的數據較多,且更新頻率相對較低,故我們采取增量更新方式的物化視圖

         (1). CREATE MATERIALIZED VIEW LOG ON INV.MTL_SYSTEM_ITEMS_B

        (2). 以INV用戶登錄,將 MLOG$_MTL_SYSTEM_ITEMS_B(執行第1步后會生成此表)授權給apps

         (3). 以apps用戶登錄,創建同義詞 MLOG$_MTL_SYSTEM_ITEMS_TL

         (4). 登錄目的數據庫

              CREATE MATERIALIZED VIEW mtl_system_items_b -- ENABLE QUERY REWRITE
                     REFRESH FAST  ON DEMAND WITH ROWID
                     START WITH SYSDATE 
                     NEXT  SYSDATE+1/2
              as
　                 　SELECT a.* FROM mtl_system_items_b@dblink a;

        create unique index mtl_system_items_b_U1 on mtl_system_items_b (ORGANIZATION_ID,INVENTORY_ITEM_ID);
        create index mtl_system_items_b_N1 on mtl_system_items_b (ORGANIZATION_ID,Segment1 );

        注: 以上步驟即完成創建增量更新的物化視圖