物化视图学习2

翻译

aoerqilengbai 2021-09-08 09:25:03 博主文章分类：oracle管理

文章标签 oracle 物化视图数据仓库存储过程数据 文章分类 代码人生

物化视图可以用在数据仓库，决策支持，分布式或移动计算中：

1咋数据仓库中，物化视图用来计算和存储聚合数据，像是sum和average。他们也可以被用来计算带聚合或不带聚合的连接。

2在分布式环境，物化视图用来复制数据。

3在移动i计算环境下，物化视图用来从中央服务器下载数据子集到移动客户端。

物化视图可以被分区。物化视图的刷新

物化视图的刷新可以是增量（快速）或完全刷新。对于快速刷新的方法，物化视图日志或直接加载日志存放主表改变的记录。物化视图的刷新可以使on demand或是一个常规时间间隔。强制刷新，当快速刷新不能实现，就强制一个完全刷新。还有一个可选的是，物化视图与主表在一个数据库中，可以在主表上事务提交后刷新。

每一个物化视图日志与单个主表想关联。物化视图日志存放在主表相同的用户下。

物化视图也可以分为只读物化视图，可更新物化视图，可写物化视图

只读物化视图：当你在创建物化视图的时候没有指定for update，那么这个视图就是一个只读物化视图。只读物化视图使用与可更新事务相同的机制，除了只读视图不必属于一个物化视图组。

可更新物化视图：在创建的时候包含for update语句，可更新视图上的改变会在刷新的时候推送回主表。可更新物化视图必须属于一个物化视图组。

下面是一个列子，首先创建可读物化视图

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
SELECT * FROM hr.departments@orc1.world;
创建物化视图组

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/

下面的语句将hr.departments物化视图加到物化视图组中，使物化视图可更新

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;

可写的物化视图

可写的物化视图是使用for update创建的物化视图，但是不是物化视图组的一部分。用户可以在可写物化视图上做dml操作，但是如果你更新物化视图，这些改变不会推送回主表。

物化视图分类：

带聚合的物化视图

只包含连接的物化视图

嵌套物化视图

在数据仓库中，物化视图通常包含聚合，为了快速刷新，如果有group by,那么select列表中必须包含所有的group by列，并且必须要有count(*),和任意的一个聚合列的count(column)，并且物化视图日志要在所有相关的查询定义涉及的表上要有。

快速刷新物化视图的刷新方法可以是on commit，on demand, refresh on commit的物化视图当事务在主表上提交的时候自动刷新。这个提交的时间就要长点了，因为刷新的动作被当做是提交过程的一部分。因此在主表上有很多并发的情况不适用这个刷新方法。

下面是对于聚合的物化视图的要求

Table 8-2 Requirements for Materialized Views with Aggregates

If aggregate X is present, aggregate Y is required and aggregate Z is optional
X	Y	Z
`COUNT(expr)`	`-`	`-`
`MIN(expr)`
`MAX(expr)`
`SUM(expr)`	`COUNT(expr)`	`-`
`SUM(col)`, `col` has `NOT` `NULL` constraint	-
`AVG(expr)`	`COUNT(expr)`	`SUM(expr)`
`STDDEV(expr)`	`COUNT(expr) SUM(expr)`	`SUM(expr * expr)`
`VARIANCE(expr)`	`COUNT(expr) SUM(expr)`	`SUM(expr * expr)`

count(*) 对于所有类型的快速刷新都是必须的。oracle建议你包含可选的聚合在物化视图中，以便获取最有效和最精确的快速刷新。

只包含连接的物化视图

有些物化视图只包含连接，没有聚合，这种类型的好处是代价高昂的连接可以提前计算

当你指定了refresh fast,oracle会检查下面的2项：

1物化视图日志要有，rowid列一定要在物化视图日志中

2所有主表的rowid一定要在物化视图查询定义中的select中出现。

当其中额一些限制不被满足，你可以创建refresh force的物化视图。

Example 8-4 Materialized View Containing Only Joins

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv 
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
       c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c 
WHERE  s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);

Alternatively, if the previous example did not include the columns times_rid and customers_rid, and if the refresh method was REFRESH FORCE, then this materialized view would be fast refreshable only if the sales table was updated but not if the tables times or customers were updated.

CREATE MATERIALIZED VIEW detail_sales_mv 
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE AS
SELECT s.rowid "sales_rid", c.cust_id, c.cust_last_name, s.amount_sold,
   s.quantity_sold, s.time_id
FROM sales s, times t, customers c 
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);

物化视图日志

对于快速刷新，物化视图之日中必须指定rowid语句。对于聚合物化视图，必须包含物化视图涉及表中的每一列，还要有including new values 和sequence语句。

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;

分析物化视图的能力

你可以使用dbms_mview.explain_mview存储过程来了解一个物化视图，包含下面的：

1这个物化视图是否能快速刷新

2你可以使用这个物化视图来查询重写什么样类型的查询

3pct刷新是否可能

这个结果默认的放在了mv_capabilities_table，或是msg_array中，在使用这个存储过程之前，一定要运行utlxmv.sql脚本。

使用这个存储过程的例子

1创建物化视图

CREATE MATERIALIZED VIEW cal_month_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

2EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');

3SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
要是相应的功能没有满足，那么N会出现在p列。