存在外连接的物化视图

ORACLE中的物化（实体）视图[http://blog.sina.com.cn/s/blog_4b05c12a0100qm89.html]

 

 

 物化视图是包括一个查询结果的数据库对像，它是远程数据的的

本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基

于远程表的数据，也可以称为快照。

 

 物化视图可以查询表，视图和其它的物化视图。

 

 通常情况下，物化视图被称为主表（在复制期间）或明细表（在

数据仓库中）。

 

 对于复制，物化视图允许你在本地维护远程数据的副本,这些副本

是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想

从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

 

 对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表

聚合视图和连接视图。

 

本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。

 

 在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询

视图。

 

1.主键物化视图：

 

 下面的语法在远程数据库表emp上创建主键物化视图

 

 SQL> CREATE MATERIALIZED VIEW mv_emp_pk

 

 REFRESH FAST START WITH SYSDATE

 

 NEXT SYSDATE + 1/48

 

 WITH PRIMARY KEY

 

 

 AS SELECT * FROM emp@remote_db;

 

 Materialized view created.

 

 注意：当用FAST选项创建物化视图，必须创建基于主表的视

图日志,如下:

 

 SQL> CREATE MATERIALIZED VIEW LOG ON emp;

 

 Materialized view log created.

 

2.Rowid物化视图

 

 下面的语法在远程数据库表emp上创建Rowid物化视图

 

 SQL> CREATE MATERIALIZED VIEW mv_emp_rowid

 

 REFRESH WITH ROWID

 

 AS SELECT * FROM emp@remote_db;

 

 Materialized view log created.

 

3.子查询物化视图

 

 下面的语法在远程数据库表emp上创建基于emp和dept表的

子查询物化视图

 

 SQL> CREATE MATERIALIZED VIEW mv_empdept

 

 AS SELECT * FROM emp@remote_db e

 

 WHERE EXISTS

 

 (SELECT * FROM dept@remote_db d

 

 WHERE e.dept_no = d.dept_no)

 

 Materialized view log created.

 

 REFRESH 子句

 

 

 [refresh [fast|complete|force]

 

 [on demand | commit]

 

 [start with date] [next date]

 

 [with {primary key|rowid}]]

 

 

 

 Refresh选项说明:

 

a. oracle用刷新方法在物化视图中刷新数据.

 

b. 是基于主键还是基于rowid的物化视图

 

c. 物化视图的刷新时间和间隔刷新时间

 

 

 

Refresh方法-FAST子句

 

 增量刷新用物化视图日志（参照上面所述）来发送主表已经修改

的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对

主表创建物化视图日志

 

 SQL> CREATE MATERIALIZED VIEW LOG ON emp;

 

Materialized view log created.

 

 对于增量刷新选项，如果在子查询中存在分析函数，则物化视图

不起作用。

 

 

 

Refresh方法- COMPLETE子句

 

 完全刷新重新生成整个视图，如果请求完全刷新，oracle会完成

完全刷新即使增量刷新可用。

 

 

 

 

Refresh Method – FORCE 子句

 

 当指定FORCE子句，如果增量刷新可用Oracle将完成增量刷新，

否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or

FORCE),Force选项是默认选项

 

 

 

主键和ROWD子句

 

 WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图

是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY

KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义

主键，否则应该用基于ROWID的物化视图.

 

 主键物化视图允许识别物化视图主表而不影响物化视图增量刷新

的可用性。

 

 Rowid物化视图只有一个单一的主表，不能包括下面任何一项:

 

 Distinct 　或者聚合函数.

 

 Group by　，子查询，连接和SET操作

 

 

 

刷新时间

 

 START WITH子句通知数据库完成从主表到本地表第一次复制的

时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的

间隔时间.

 

 SQL> CREATE MATERIALIZED VIEW mv_emp_pk

 

 

 REFRESH FAST

 

 START WITH SYSDATE

 

 NEXT SYSDATE + 2

 

 WITH PRIMARY KEY

 

 AS SELECT * FROM emp@remote_db;

 

 Materialized view created.

 

在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每

两天刷新一次.

 

 

 

总结

 

物化视图提供了可伸缩的基于主键或ROWID的视图,指定了刷新方

法和自动刷新的时间。

 

 

 

 

 

物化视图的效率

 

有两个表:人员表A001有10万数据，机构表B001有5千数据

 

建普通视图：

 

create or replace view test_v as

select * from A001 a ,B001 B where a.a001721=b.orguid

 

建一个物化视图

 

 

create MATERIALIZED view test_v_m as

select * from A001 a ,B001 B where a.a001721=b.orguid

 

比较效率：

 

select * from test_v where A001001 like '%军%' or

b001003 like '1006%' or A001011 like '70%' ；用

时10秒多

 

from test_v_m where A001001 like '%军%' or b001003 like

'1006%' or A001011 like '70%' ;用时0.125秒

 

对查询来说，物化视图的效率是普通视图的10/0.125=80倍；相

当高啊！

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

创建多表关联的实体化视图

 

某些实体化视图包含多表关联，但不包含聚合操作

 

(例如sum())

 

例如

 

Example 8-4

 

实体化视图是在 sales 表和times,customers 三张表的关联的基础上

创建的。创建这种类型的实体化视图的好处是预先处理了代价高昂

的关联操作。在任意类型的 DML操作之后，使用fast refresh 的方

式来刷新仅包含表关联的实体化视图是可以实现的。

 

仅包含表关联的实体化视图同样支持在两种情况下刷新：

 

 ON COMMIT 和 ON DEMAND.在 ON COMMIT 下，当基表发生

DML操作并提交commit 的时候，实体化视图被刷新。

 

Oracle不允许实体化视图中有self-jions 的存在。

 

假如指定刷新方式为

 

REFRESH FAST, oracle 执行附加的校验，以确定fast refresh能被执

行。这些附加的检查包括：

 

1 每个基表下必须创建相应的 materialized view log

 

2 每个基表的rowid 必须在实体化视图的select 语句中出现

 

3如果存在外联接，在where 子句不能有任何的selections.并且，所

有的关联必须以 AND 相连，并且用 “=”操作符。

 

4如果存在外联接，唯一性约束必须存在于外联的 inner table 的相关

列中。

 

 

为了提高刷新的速度，必须在实体化视图上创建基表的 rowed 所在

列的索引。

 

 

 

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(+);

 

在这个示例中，为了执行

 

fast refresh ,

 

唯一性约束必须存在于

 

c.cust_id and t.time_id.

 

必须创建

 

sales_rid, times_rid, and customers_rid

 

各自的索引，如此可以提高刷新的速度。

 

CREATE INDEX mv_ix_salesrid ON detail_sales_mv("sales_rid");

 

 

 

Materialized Views with Aggregates

 

 

 

 

创建聚合类型的实体化视图。

 

 

 

在数据仓库中，实体化视图中经常会包括一些聚合操作。为了能够进

行快速刷新(fast refersh) ，在select 语句中必须包含所有在group by

 

中出现的列名，并且必须有一个count(*) ，所有在gorup by 中出现

的列都必须有一个 count(列名)出现在select 中。同样，所有于实体

化视图相关的表上都必须创建实体化和视图日志。有效的聚合函数包

括：

 

 

 

SUM

 

,

 

COUNT(x)

 

,

 

COUNT(*)

 

,

 

AVG

 

,

 

VARIANCE

 

,

 

STDDEV

 

,

 

 

MIN

 

, and

 

MAX ,

 

被聚合的值可以是任何的

 

sql

 

表达式。

 

Fast refresh

 

一个包含关联和聚合的实体化视图是可能的。但有如下限制：

 

1

 

 

 

实体化视图中不能包含非复用的表达式

 

(

 

具有实时特征的

 

) 。比如

 

 

 

SYSDATE and ROWNUM.

 

2

 

 

 

实体化视图中不能包含

 

 

 

RAW or LONG 数据类型。

 

3

 

 

实体化视图相关的表必须创建实体化视图日志，并且满足如下条件

 

包含被实体化视图引用到的所有的列。

 

指定

 

 

 

with rowed和INCLUDING NEW VALUES.

 

如果表的预期操作是

 

inserts/direct-loads, deletes, and updates的混合，指定SEQUENCE 子

句只用

 

 

 

SUM

 

,

 

COUNT

 

,

 

AVG

 

,

 

STDDEV

 

,

 

VARIANCE

 

,

 

MIN

 

 and

 

MAX

 

 

支持

 

 fast refresh.

 

对于每个聚合

 

 agg(expr),

 

必须相应写一个

 

 count(expr)

 

与之对应

 

如果VARIANCE(expr)or STDDEV(expr)被指定，那么

COUNT(expr)and SUM(expr) 必须被指定。Oracle推荐 SUM(expr

*expr) 也被指定。在

 

select列表中必须包含所有group by

 

中出现的列.如果实体化视图有如下的一点，那么fast refresh 只支持

常规DML inserts

 

和直接路径的装载

 

(

 

direct loads

 

)

 

 

 

a

 

 

 

实体化视图包含MIN 或者 MAX

 

 

 

 

b 实体化视图包含SUM(expr)

 

 但是没有COUNT(expr)

 

 

 

c

 

实体化视图中不包含

 

 COUNT(*)如上的实体化视图称之为insert-only materialized view.

 

包含

 

MAX or MIN实体化视图在delete或者混合的

 

DML

 

操作之后可以是 fast refresh，条件是视图中没有where 子句。

 

(10g

 

以上适用

 

)

 

如果没有外联接，在where 子句中可以包含有选择性的条件和关联

 

 如果实体化视图中包含外联接，fast refresh只支持在outer table 被

修改的情况。同样，唯一性约束必须存在于

 

inner join table中。如果有外联，所有的关联条件都必须用 AND 连

接和必须用 = 操作符。

 

Example 8-1

 

Example 1: Creating a Materialized View

 

CREATE MATERIALIZED VIEW LOG ON products WITH

SEQUENCE, ROWID

 

 

(prod_id, prod_name, prod_desc, prod_subcategory,

prod_subcategory_desc,

 

prod_category, prod_category_desc, prod_weight_class,

prod_unit_of_measure,

 

 prod_pack_size, supplier_id, prod_status, prod_list_price,

prod_min_price)

 

INCLUDING NEW VALUES;

 

CREATE MATERIALIZED VIEW LOG ON sales

 

WITH SEQUENCE, ROWID

 

(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold,

amount_sold)

 

INCLUDING NEW VALUES;

 

CREATE MATERIALIZED VIEW product_sales_mv

 

PCTFREE 0

 

 

 

TABLESPACE demo

 

STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)

 

BUILD IMMEDIATE

 

REFRESH FAST

 

ENABLE QUERY REWRITE

 

AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,

 

COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt

 

 

FROM sales s, products p

 

WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;

 

Example 8-3 Example 3:

 

Creating a Materialized View

 

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE,

ROWID

 

(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold,

amount_sold)

 

INCLUDING NEW VALUES;

 

CREATE MATERIALIZED VIEW sum_sales

 

PARALLEL

 

BUILD IMMEDIATE

 

 

 

REFRESH FAST ON COMMIT AS

 

 

 

SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp,

 

 

 

SUM(s.amount_sold) AS sum_dollar_sales,

 

 

 

COUNT(s.amount_sold) AS count_dollar_sales,

 

 

 

SUM(s.quantity_sold) AS sum_quantity_sales,

 

 

 

 

COUNT(s.quantity_sold) AS count_quantity_sales

 

FROM sales s

 

GROUP BY s.prod_id, s.time_id;