物化檢視開發相關問題指導意見

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. 如果物化檢視是包含連線的，還需要在基表的連線欄位上建立索引（一般都會有吧？）。請大家複核一下。