oracle物化视图心得(转)

14年前

业务描述: 外围系统需要访问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 );

        注: 以上步骤即完成创建增量更新的物化视图