物化视图日志 快速刷新实验

//快速刷新与物化视图日志

DROP MATERIALIZED VIEW sales_mv;

CREATE MATERIALIZED VIEW sales_mv

ENABLE QUERY REWRITE

AS

SELECT p.prod_category, c.country_id,

       sum(s.quantity_sold) AS quantity_sold,

       sum(s.amount_sold) AS amount_sold

FROM sales s, customers c, products p

WHERE s.cust_id = c.cust_id

AND s.prod_id = p.prod_id

GROUP BY p.prod_category, c.country_id;  //GROUP BY 列表多的那个字段 去掉了 改回了原来的定义。。

ALTER MATERIALIZED VIEW sales_mv REFRESH FAST ON DEMAND;

此时会报 ora-23413 ，因为没有物化视图日子。。MV LOG

 

 2 完全刷新（REFRESH COMPLETE）:容器表的所有内容都会被删除，并且所有的数据都会从基础表重新加载只有在相当一部分数据被修改后才去使用这种方式。

 1 快速刷新（REFRESH FAST）:容器表中的内容会被重用，只有被修改的数据才会被同步到容器表。 如果基础表上只有少量数据被变更，就应该使用这种方法。效率高，但是限制大。

 3 强制刷新（REFRESH FORCE）:当强制刷新时，最初，会尝试使用快速刷新。如果它无法工作，就执行一次完全刷新。

 

#为了激活快速刷新，必须在每个基础表上都创建一个物化视图日志（materialized view log）MV LOG是快速刷新的基本条件 没有它 就无从谈快速刷新了

创建mv log

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON products WITH ROWID;

 

在MV的每张基表上都建立 物化视图日志 ，要满足快速刷新，则物化视图的每个基表都必须创建物化视图日志 

 

SELECT master, log_table FROM user_mview_logs

WHERE master IN ('SALES', 'CUSTOMERS', 'PRODUCTS')

AND log_owner = 'SH';

desc mlog$_sales

 

但是如此初级的一个物化视图日志(WITH ROWID)还不足以支持快速刷新，还需要进行一些附加的处理 

具体还需要的要求可以使用包dbms_mview里的存储过程explain_mview来查找 

 

TRUNCATE TABLE mv_capabilities_table; 

execute dbms_mview.explain_mview(mv => 'sales_mv', stmt_id => '42'); 

col MSGTXT for a50

SELECT capability_name, possible, msgtxt, related_text

FROM mv_capabilities_table

WHERE statement_id = '42'

AND capability_name LIKE 'REFRESH_FAST_AFTER%'

ORDER BY seq;

//要解决与物化视图日志相关的这些问题，就必须在执行语句CREATE MATERIALIZED VIEW LOG时添加一些选项：

1、如果是由于物化视图日志没有包含所有的必要字段（MV log does not have all necessary columns），则你必须规定物化视图引用的每一个字段都要保存到物化视图日志中 

2、如果是由于物化视图日志必须有新值（MV log must have new values），则必须使用INCLUDING NEW VALUES子句将这些字段加上。通过使用这个选项，物化视图日志将会同时保存这些字段的新值与旧值（默认情况下只会保存旧值） 

3、如果是由于物化视图日志没有包含序列号（MV log does not have sequence），则有必要添加SEQUENCE子句。通过使用这个选项，就会有一个序列值与存储在物化视图日志中的记录关联在一起了 

 

重新定义物化视图日志：

DROP MATERIALIZED VIEW LOG ON sales;

DROP MATERIALIZED VIEW LOG ON customers;

DROP MATERIALIZED VIEW LOG ON products; 

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE

(cust_id, prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID, SEQUENCE

(cust_id, country_id) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE

(prod_id, prod_category) INCLUDING NEW VALUES; 

 

TRUNCATE TABLE mv_capabilities_table;

 

execute dbms_mview.explain_mview(mv => 'sales_mv', stmt_id => '42')

SELECT capability_name, possible, msgtxt, related_text

FROM mv_capabilities_table

WHERE statement_id = '42'

AND capability_name LIKE 'REFRESH_FAST_AFTER%'

ORDER BY seq;

//除了物化视图日志以外，我们还得解决物化视图本身的问题 ,要解决物化视图本身的问题，必须在物化视图创建的时候添加几个基于函数count的新字段。 

 

#下面的SQL语句显示了包含新字段的物化视图定义 

drop materialized view sales_mv; 

CREATE MATERIALIZED VIEW sales_mv

REFRESH FORCE ON DEMAND

AS

SELECT p.prod_category, c.country_id,

       sum(s.quantity_sold) AS quantity_sold,

       sum(s.amount_sold) AS amount_sold,

       count(*) AS count_star,

       count(s.quantity_sold) AS count_quantity_sold,

       count(s.amount_sold) AS count_amount_sold

FROM sales s, customers c, products p

WHERE s.cust_id = c.cust_id

AND s.prod_id = p.prod_id

GROUP BY p.prod_category, c.country_id;

 

#MV的定义里 新增了COUNT字段,再分析一遍

truncate table mv_capabilities_table; 

exec dbms_mview.explain_mview(mv => 'sales_mv',stmt_id => '42'); 

SELECT capability_name, possible, msgtxt, related_text

FROM mv_capabilities_table

WHERE statement_id = '42'

AND capability_name LIKE 'REFRESH_FAST_AFTER%'

ORDER BY seq; 

//结果显示在所有情形下物化视图都支持快速刷新了（字段possible总是被设置成Y）

//下面可以通过往两张表中插入记录并执行快速刷新来测试一下快速刷新究竟有多快

 

set timing on

 

INSERT INTO products 

SELECT 619, prod_name, prod_desc, prod_subcategory,  prod_subcategory_id,

       prod_subcategory_desc, prod_category, prod_category_id,

       prod_category_desc, prod_weight_class, prod_unit_of_measure,

       prod_pack_size, supplier_id, prod_status, prod_list_price, 

       prod_min_price, prod_total, prod_total_id, prod_src_id, 

       prod_eff_from, prod_eff_to, prod_valid

FROM products

WHERE prod_id = 136;

 

 

INSERT INTO sales

SELECT 619, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold

FROM sales

WHERE prod_id = 136;

Commit；

 

execute dbms_mview.refresh(list => 'sh.sales_mv', method => 'f');

DELETE sales WHERE prod_id = 619;

DELETE products WHERE prod_id = 619;

 

COMMIT;

 

SET TIMING ON

execute dbms_mview.refresh(list => 'sh.sales_mv', method => 'f');

//可以把这个快速刷新的时间和完全刷新的时间比对；

快速刷新不是通过CBO获取信息的 而是通过MV LOGS

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

转载于:http://blog.itpub.net/22907091/viewspace-713564/