Oracle之物化視圖

標籤：blog   http   io   os   使用   ar   for   資料   2014   

http://www.cnblogs.com/Ronger/archive/2012/03/28/2420962.htmlOracle之物化視圖

近期根據項目業務需要對oracle的物化視圖有所接觸，在網上搜尋關於這方面的資料，便於提高，整理內容如下：

物化視圖是一種特殊的物理表，“物化”(Materialized)視圖是相對普通視圖而言的。普通視圖是虛擬表，應用的局限性大，任何對視圖的查詢，Oracle都實際上轉換為視圖SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。
1、物化視圖的類型：ON DEMAND、ON COMMIT

    二者的區別在於重新整理方法的不同，ON DEMAND顧名思義，僅在該物化視圖“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化視圖，以保證和基表資料的一致性；而ON COMMIT是說，一旦基表有了COMMIT，即事務提交，則立刻重新整理，立刻更新物化視圖，使得資料和基表一致。
2、ON DEMAND物化視圖   

物化視圖的建立本身是很複雜和需要最佳化參數設定的，特別是針對大型生產資料庫系統而言。但Oracle允許以這種最簡單的，類似於普通視圖的方式來做，所以不可避免的會涉及到預設值問題。也就是說Oracle給物化視圖的重要定義參數的預設值處理是我們需要特別注意的。   

物化視圖的特點：

     (1) 物化視圖在某種意義上說就是一個物理表(而且不僅僅是一個物理表)，這通過其可以被user_tables查詢出來，而得到佐證；

    (2) 物化視圖也是一種段(segment)，所以其有自己的實體儲存體屬性；

     (3) 物化視圖會佔用資料庫磁碟空間，這點從user_segment的查詢結果，可以得到佐證；

    建立語句：create materialized view mv_name as select * from table_name    預設情況下，如果沒指定重新整理方法和重新整理模式，則Oracle預設為FORCE和DEMAND。
    物化視圖的資料怎麼隨著基表而更新？    Oracle提供了兩種方式，手工重新整理和自動重新整理，預設為手工重新整理。也就是說，通過我們手工的執行某個Oracle提供的系統級預存程序或包，來保證物化視圖與基表資料一致性。這是最基本的重新整理辦法了。自動重新整理，其實也就是Oracle會建立一個job，通過這個job來調用相同的預存程序或包，加以實現。

       ON DEMAND物化視圖的特性及其和ON COMMIT物化視圖的區別，即前者不重新整理(手工或自動)就不更新物化視圖，而後者不重新整理也會更新物化視圖，——只要基表發生了COMMIT。

    建立定時重新整理的物化視圖：create materialized view mv_name refresh force on demand start with sysdate next sysdate+1 （指定物化視圖每天重新整理一次）

     上述建立的物化視圖每天重新整理，但是沒有指定重新整理時間，如果要指定重新整理時間（比如每天晚上10：00定時重新整理一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,‘dd-mm-yyyy‘),‘ 22:00:00‘),‘dd-mm-yyyy hh24:mi:ss‘)
3、ON COMMIT物化視圖

     ON COMMIT物化視圖的建立，和上面建立ON DEMAND的物化視圖區別不大。因為ON DEMAND是預設的，所以ON COMMIT物化視圖，需要再增加個參數即可。   需要注意的是，無法在定義時僅指定ON COMMIT，還得附帶個參數才行。

     建立ON COMMIT物化視圖：create materialized view mv_name refresh force on commit as select * from table_name    備忘：實際建立過程中，基表需要有主鍵約束，否則會報錯（ORA-12014）

4、物化視圖的重新整理

     重新整理（Refresh）：指當基表發生了DML操作後，物化視圖何時採用哪種方式和基表進行同步。重新整理的模式有兩種：ON DEMAND和ON COMMIT。（如上所述）

     重新整理的方法有四種：FAST、COMPLETE、FORCE和NEVER。FAST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE重新整理對整個物化視圖進行完全的重新整理。如果選擇FORCE方式，則Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用FAST方式，否則採用COMPLETE的方式。NEVER指物化視圖不進行任何重新整理。

      對於已經建立好的物化視圖，可以修改其重新整理方式，比如把物化視圖mv_name的重新整理方式修改為每天晚上10點重新整理一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,‘dd-mm-yyyy‘),‘ 22:00:00‘),‘dd-mm-yyyy hh24:mi:ss‘)    
5、物化視圖具有表一樣的特徵，所以可以像對錶一樣，我們可以為它建立索引，建立方法和對錶一樣。
6、物化視圖的刪除：

      雖然物化視圖是和表一起管理的，但是在經常使用的PLSQL工具中，並不能用刪除表的方式來刪除（在表上右鍵選擇‘drop’並不能刪除物化視圖），可以使用語句來實現：drop materialized view mv_name 

物化視圖建立參數

 (1)BUILD BUILD IMMEDIATE 是在建立物化視圖的時候就產生資料 BUILD DEFERRED 則在建立時不產生資料，以後根據需要再產生資料。 預設為BUILD IMMEDIATE。
(2)REFRESH FAST 增量重新整理用物化視圖日誌，來發送主表已經修改的資料行到物化視圖中。 COMPLETE 完全重新整理重建整個視圖，如果請求完全重新整理，oracle會完成完全重新整理即使增量重新整理可用。 FORCE 如果增量重新整理可用Oracle將完成增量重新整理，否則將完成完全重新整理，如果不指定重新整理方法(FAST, COMPLETE, or FORCE)。 預設選項是Force。
(3)ON ON DEMAND 指物化視圖在使用者需要的時候進行重新整理。 ON COMMIT 指出物化視圖在對基表的DML操作提交的同時進行重新整理。 預設是ON DEMAND.
(4)START WITH 通知數據庫完成從主表到本地表第一次複製的時間。
(5)NEXT 說明了重新整理的時間間隔 根據下一次重新整理的時間=上一次執行完成的時間+時間間隔。 為了保證在使用者需要的時間點重新整理，一般使用TRUNC()命令對時間取整到天數，然後加上時間。

具體範例程式碼如下所示：

/* Formatted on 2012/3/28 11:26:08 (QP5 v5.149.1003.31008) */
--刪除日誌
TRUNCATE TABLE mlog$_fe_fee;
DROP MATERIALIZED VIEW LOG ON fe_fee;
TRUNCATE TABLE mlog$_fe_order;
DROP MATERIALIZED VIEW LOG ON fe_order;
TRUNCATE TABLE mlog$_fe_job;
DROP MATERIALIZED VIEW LOG ON fe_job;
TRUNCATE TABLE mlog$_fi_acc_bill;
DROP MATERIALIZED VIEW LOG ON fi_acc_bill;
TRUNCATE TABLE mlog$_fi_acc_fee;
DROP MATERIALIZED VIEW LOG ON fi_acc_fee;
TRUNCATE TABLE mlog$_fe_fee_age;
DROP MATERIALIZED VIEW LOG ON fe_fee_age;

--建立基表日誌
CREATE MATERIALIZED VIEW LOG ON fe_fee WITH ROWID, SEQUENCE(job_id, order_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_order WITH ROWID, SEQUENCE( order_id)INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_job WITH ROWID ,SEQUENCE(job_id)INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fi_acc_bill WITH ROWID, SEQUENCE(bill_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fi_acc_fee WITH ROWID, SEQUENCE(fee_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON fe_fee_age WITH ROWID, SEQUENCE(job_id, order_id) INCLUDING NEW VALUES;

--建立物化視圖
DROP MATERIALIZED VIEW mv_job_fee;


CREATE MATERIALIZED VIEW mv_job_fee
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 5/(60*24)
AS
SELECT f.ROWID fi, j.ROWID ji, o.ROWID oi, b.ROWID bi, c.ROWID ci, f.fee_id,
       f.job_id, f.order_id, f.fee_type, f.fee_code, f.unit_price, f.quantity,
       f.currency, f.fx_rate, f.cust_id, f.invoice_num, f.is_confirm,
       f.blunt_flag, f.verify_balance, f.is_agreement, f.fiscal_period,
       f.attribute, f.continue, f.remark, f.security, f.create_by,
       f.create_time, f.pay_type, f.sharing_type, f.bill_id, f.direction,
       f.profit_loses, f.unit, f.relation_cust, f.amount, f.fx_amout,
       f.modified_by, f.modified_date, f.proportion, f.job_period,
       o.quantity ord_quantity, o.gross_weight ord_gross_weight,
       o.volume ord_volume, o.charge_weight ord_charge_weight,
       o.custom_num ord_custom_num, o.pay_type ord_pay_type,
       o.pay_type2 ord_pay_type2, o.teu ord_teu,
       o.cust_service ord_cust_service, o.oper ord_oper, o.bill ord_bill,
       o.sales ord_sales, o.cust_id ord_cust_id, o.bill_no  bill_no , 1  AS  ord_canvassing,1  AS ord_agent_type,
       j.dept_id job_dept_id,
       j.job_type job_job_type, j.firm job_firm, j.way_bill job_way_bill,
       j.loading job_loading, j.discharging job_discharging, j.etd job_etd,
       j.eta job_eta, j.flight_num job_flight_num, j.provider job_provider,
       j.carrier job_carrier, j.voyage job_voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu,
       j.fee_lock job_fee_lock, j.lock_time job_lock_time,
       j.auditor job_auditor, j.archiveno job_archiveno,
       j.archived_by job_archived_by, j.archived_time job_archived_time,
       j.oversea_agent job_oversea_agent, j.container_info job_container_info,
       j.container_num job_container_num, j.proj_id job_proj_id,
       j.route  job_route,
       b.book_date bill_book_date, b.commit_flag bill_commit_flag,
       b.pay_period bill_pay_period, b.invoice_rise, c.confirm_amount,
       c.confirm_time
  FROM fe_fee f, fe_order o, fe_job j, fi_acc_bill b, fi_acc_fee c
 WHERE     f.job_id = j.job_id(+)
       AND f.order_id = o.order_id(+)
       AND f.bill_id = b.bill_id(+)
       AND f.fee_id = c.fee_id(+);



DROP MATERIALIZED VIEW mv_order_cargo;

CREATE MATERIALIZED VIEW mv_order_cargo
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 10/(60*24)
AS
SELECT j.ROWID ji, o.ROWID oi, o.order_id, o.job_type, o.cust_id, o.dept_id,
       o.firm, o.job_id, o.quantity, o.gross_weight, o.volume,
       o.charge_weight, o.custom_num, j.loading, j.discharging, o.pay_type,
       o.pay_type2, o.teu, o.cust_service, o.oper, o.bill, o.sales,
       o.booking_type, o.route, o.assign_agent, j.way_bill, j.etd, j.eta,
       j.flight_num, j.provider, j.carrier, j.voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu, j.job_period,
       j.oversea_agent, j.container_info, j.container_num
  FROM fe_order o, fe_job j
 WHERE o.job_id = j.job_id(+);

DROP MATERIALIZED VIEW mv_fee_age;

CREATE MATERIALIZED VIEW mv_fee_age
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 5/(60*24)
AS
SELECT a.ROWID ai, f.ROWID fi, j.ROWID ji, o.ROWID oi, a.fee_id,
       a.job_id, a.order_id, f.fee_type, f.fee_code, f.unit_price, f.quantity,
       f.currency, f.fx_rate, f.cust_id, f.invoice_num, f.is_confirm,
       f.blunt_flag, f.verify_balance, f.is_agreement, f.fiscal_period,
       f.attribute, f.continue, f.remark, f.security, f.create_by,
       f.create_time, f.pay_type, f.sharing_type, f.bill_id, f.direction,
       f.profit_loses, f.unit, f.relation_cust, f.amount, f.fx_amout,
       f.modified_by, f.modified_date, f.proportion, f.job_period,
       o.quantity ord_quantity, o.gross_weight ord_gross_weight,
       o.volume ord_volume, o.charge_weight ord_charge_weight,
       o.custom_num ord_custom_num, o.pay_type ord_pay_type,
       o.pay_type2 ord_pay_type2, o.teu ord_teu,
       o.cust_service ord_cust_service, o.oper ord_oper, o.bill ord_bill,
       o.sales ord_sales, o.cust_id ord_cust_id, j.dept_id job_dept_id,
       j.job_type job_job_type, j.firm job_firm, j.way_bill job_way_bill,
       j.loading job_loading, j.discharging job_discharging, j.etd job_etd,
       j.eta job_eta, j.flight_num job_flight_num, j.provider job_provider,
       j.carrier job_carrier, j.voyage job_voyage, j.quantity job_quantity,
       j.gross_weight job_gross_weight, j.volume job_volume,
       j.charge_weight job_charge_weight, j.teu job_teu,
       j.fee_lock job_fee_lock, j.lock_time job_lock_time,
       j.auditor job_auditor, j.archiveno job_archiveno,
       j.archived_by job_archived_by, j.archived_time job_archived_time,
       j.oversea_agent job_oversea_agent, j.container_info job_container_info,
       j.container_num job_container_num, j.proj_id job_proj_id
  FROM fe_fee_age a, fe_fee f, fe_order o, fe_job j
 WHERE a.fee_id = f.fee_id(+)
 AND a.job_id = j.job_id(+)
 AND a.order_id = o.order_id(+);

Oracle之物化視圖