ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图开发相关问题指导意见

物化视图开发相关问题指导意见

原创 Linux操作系统 作者:ljm0211 时间:2012-06-20 16:46:34 0 删除 编辑

1.创建快速刷新(fast refresh,也称增量刷新)的物化视图,必须在基表上先创建物化视图日志:

CREATE MATERIALIZED VIEW LOG ON hsfssdata.TBTRANSREQ with rowid,sequence including new values;

CREATE MATERIALIZED VIEW LOG ON hsfssdata.HIS_TBTRANSREQ with rowid,sequence including new values;

注意,此处没有用primary key,而是rowid。

 

2.包含连接的物化视图,在快速刷新的时候有bug,因为执行计划很差,导致增量刷新速度很慢,可能比全量刷新还慢好几倍(具体要看日志的数据量以及基表的数据量)。

解决办法如下:

  2.1 首先根据不同Oracle版本,设置不同的隐含参数值:

      10.1 : _mv_refresh_use_stats=FALSE

      10.2 : _mv_refresh_use_stats=TRUE

      9.X  : 9i太复杂了,稍后单独处理

  这一步需要DBA来做     

 

  2.2 然后将日志表清空,并且在清空状态下,收集统计信息,并锁定。

      2.2.1 清空:

      truncate table HSFSSDATA.MLOG$_TBTRANSREQ;

      truncate table HSFSSDATA.MLOG$_HIS_TBTRANSREQ;

      注: MV日志表就是原表表明前加上 MLOG$_ 前缀

     

      2.2.2 收集统计信息

        EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HSFSSDATA' , TABNAME => 'MLOG$_TBTRANSREQ');

        EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HSFSSDATA' , TABNAME => 'MLOG$_HIS_TBTRANSREQ');

      2.2.3 锁定统计信息       

        EXEC DBMS_STATS.LOCK_TABLE_STATS(OWNNAME => 'HSFSSDATA' , TABNAME => 'MLOG$_TBTRANSREQ');

        EXEC DBMS_STATS.LOCK_TABLE_STATS(OWNNAME => 'HSFSSDATA' , TABNAME => 'MLOG$_HIS_TBTRANSREQ');

 

3.创建物化视图。需注意一下几点:

  3.1 创建MV时,可以先 build deferred 而不是 build immediate,这样的话只会创建一个空壳,而不会真正产生数据。所有东西都调试好之后在全量刷新一遍,产生初始化数据。

  如果初始化数据量太大,可以用基于预见表的方式创建MV. 不建议MV使用并行(PARALLEL)

  3.2 使用 with rowid 的方式创建

  3.3 包含连接的物化视图,from 语句中出现的每一个表,都要将其 rowid 列写在select 语句中。

  3.4 不能使用子查询,不能使用sysdate

  3.5 使用UNION ALL时,对于每一段查询,都要有一个区别于其他的常量字段(即下例中的pmarker)

 

create materialized view hsfssdata.fund_tbtransreq_end_day_view

 PARALLEL BUILD deferred --IMMEDIATE

REFRESH fast --制定刷新方式:fast,complete,force ; must be include rowid whitch all tables

on demand--刷新方式:demand,commit

START WITH sysdate--第一次刷新时间

NEXT sysdate + 1/1440

with rowid

as

select

    serial_no         as "TRANSACTION_NO",  --各系统交易登记流水

    '721691'         as "SYSID",

    trans_code       as "TRANSACTION_TYPE",--交易类型(需要配置交易控制表)

    status           as "TRANSACTION_STATUS",--交易状态:01已受理、02已授权、03已审核、04处理成功、05已撤消、99已冲销

    '02'             as "BUSINESS_TYPE",    --业务种类:01现金、02转账、03其他

    bank_acc         as "PRODUCT_NO",        --产品号:账号、卡号、购证卡号

    client_no        as "CLIENT_NO",        --客户号

    amt              as "AMOUNT",            --交易金额

    curr_type        as "CURRENCY",          --交易币种

    branch_no         as "SITE_CODE",        --交易网点

    oper_no           as "PROCESSOR_ID",      --受理柜员ID

    to_date(trans_date,'yyyyMMdd')        as "PROCESS_DATE",--受理日期

    auth_oper           as "COUNTER_AUTH_ID",  --最后授权人ID

    to_date(trans_date,'yyyyMMdd')       as "COUNTER_AUTH_DATE",--最后授权日期

    'sec1'  as pmarker

     ,rowid id1

from hsfssdata.tbtransreq 

--where phi_date=to_char(sysdate,'yyyymmdd')  and

where status in('0','1','2','3','E','G','A','F','5','7','8','9','6')

and channel='6'

union all

select

    serial_no         as "TRANSACTION_NO",  --各系统交易登记流水

    '721691'         as "SYSID",

    trans_code       as "TRANSACTION_TYPE",--交易类型(需要配置交易控制表)

    status           as "TRANSACTION_STATUS",--交易状态:01已受理、02已授权、03已审核、04处理成功、05已撤消、99已冲销

    '02'             as "BUSINESS_TYPE",    --业务种类:01现金、02转账、03其他

    bank_acc         as "PRODUCT_NO",        --产品号:账号、卡号、购证卡号

    client_no        as "CLIENT_NO",        --客户号

    amt              as "AMOUNT",            --交易金额

    curr_type        as "CURRENCY",          --交易币种

    branch_no         as "SITE_CODE",        --交易网点

    oper_no           as "PROCESSOR_ID",      --受理柜员ID

    to_date(trans_date,'yyyyMMdd')        as "PROCESS_DATE",--受理日期

    auth_oper           as "COUNTER_AUTH_ID",  --最后授权人ID

    to_date(trans_date,'yyyyMMdd')       as "COUNTER_AUTH_DATE",--最后授权日期

     'sec2'  as pmarker

     ,rowid id1

from hsfssdata.his_tbtransreq 

--where trans_date=to_char(sysdate,'yyyymmdd') and

where status in('2','3','G','F','5','7','8','9','6','4')

and channel='6'

 

4. 给物化视图中 rowid 字段创建索引(物化视图增量刷新时需要使用)

create index hsfssdata.ix_mv1_id1 on hsfssdata.fund_tbtransreq_end_day_view (id1) parallel 8;

alter  index hsfssdata.ix_mv1_id1 noparallel;

上述语句为了加快创建索引的速度,开了并行度为8,大家可以根据具体的MV数据量和DB负载情况,酌情调整。

第二句将索引并行度关闭。这一点很重要,勿忘!

 

5. 如果物化视图是包含连接的,还需要在基表的连接字段上创建索引(一般都会有吧?)。请大家复核一下。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-733450/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    441982