OCM考试经验--物化视图详解及用

---======================================

--=  OCM考试经验

--=      物化视图

--=

--=======================================

一、物化视图解释

OCM考试中，物化视图所属SECTION 4,是专门检验数据仓库知识的环节，因此物化视图的主要作用就是降低复杂连接查询的资源消耗，直接在一个实体化（物化）视图中保存查询结果，在日后可以随时调用，数据仓库一般提供读取工作较多，事务性工作较少，如果基表的一次更新都需要大量复杂查询，性能显然不可以保证，物化视图则缓解这一问题

它的基本定义是：物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。 对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。物化视图可以查询表，视图和其它的物化视图。

 1、物化视图与其他概念的区别

物化视图与普通表---两者都可以在user_objects查询到相关对象，但普通表一般存储原始数据即第一手生成数据、不依赖其他表，而物化视图的结果集多是复杂的多表连接查询结果，而且某些情况下，而原始表的的更新可以根据需要及时在物化视图上体现出来，普通表则不具备这种功能。

  2、物化视图与普通视图的区别

普通视图并不在user_objects产生相应对象，只在数据库中建立查询定义且不具备降低资源消耗的功能，每次查询视图都要重新执行视图的定义，多数用于为远程登录用户提供数据只读查询的转换。

二、物化视图的使用

   1、准备条件：

创建相应用户，并且需要dba赋予grant  create materialized view to xxx

   2、创建物化视图日志:

当使用快速刷新的时候，对基表的所有更改都是先记录在物化视图日志当中，当执行相应的刷新操作时，由日志更新物化视图，

之后日志清空物化视图日志表的名称为mlog$_后面跟基表的名称，且物化视图必须包含基表主键，且基表会有主键，如果不包括，会提示错误。

对多表联合查询时，需要为每个表创建日志

   3、创建物化视图

            基本语句

       create materialized view xxx

          选项

创建时间：build immediate   build deferred

Build immediate:在创建物化视图的同时根据主表生成数据

Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取完全刷新即可

Refresh子句选项：（complete|fast|force）

--Refresh方法-COMPLETE子句

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

--Refresh方法-FAST子句--必须创建物化视图日志

       增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESHFAST子句，那么应该对主表创建物化视图日志

--Refresh Method – FORCE 子句

       当指定FORCE子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新,如果不指定刷新方法(FAST,COMPLETE, or

FORCE),Force选项是默认选项

刷新时间：on demand ||on commit ||start with/ next

On demand:在需要刷新时进行刷新（人工判断）

On commit:在基表上有提交操作时，进行更新

Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）

Next:刷新的周期时间

 START WITH SYSDATE

                 NEXT SYSDATE + 2

开启重写

enable rewrite query --该参数与系统参数有关，当数据库参数query rewrite enable为true时，并且运行在CBO模式下，

当有对基表的数据查询时，系统检测到当前基表有包含enable rewrite query关键字的物化视图时，系统自动判断是否可通过物化视图进行代价更小的查询。

重点参数主键primary key与Rowid物化视图

 WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). 

PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图。

Rowid物化视图只有一个单一的主表。 该参数需和物化视图日志想同，否则报错

可更新物化视图

for update--当使用该参数时，可对单表物化视图进行更新，但无法体现在源表上

刷新语句

exec dbms_mview.refresh(mv_name, 'c') --手动完全刷新

exec dbms_mview.refresh(mv_name, 'f') --手动快速刷新

四、示例

-----使用增量刷新的物化视图的写法创建物化视图日志，必须创建日志

Create materialized view log on TEST ----TEST为表名

----注：(TEST为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图

----创建物化视图语句：

Create materialized view MV_TEST

----MVTEST为物化视图名

Build immediate

----创建时生成数据对应的是build deferred

Refresh fast

----增量刷新

On commit

----在基表有更新时提交，这里该句对视图无效

With rowid

----这里创建基于rowid的物化视图，对应的是 primary key

As

Select * from TEST;

----生成物化视图数据语句

五、关于多表查询，去重函数的刷新说明

关于单表查询去重问题可以使用多种方式，count(*)+groupby，distinct

count(*)+groupby -- 支持自动/手动 完全/快速刷新操作

distinct ---仅支持完全刷新

当使用聚合函数时，刷新方式

sum(x-y)--不可进行快速刷新

sum(x-y)+count(*) ---不可进行快速刷新

sum(x-y)+count(*) +coumt(x-y) --可进行完全/快速刷新

六、on prebuilt table

使用on prebuilt table 在备库已存在的实体表上创建物化视图， 此时备库上有一个与源表数据结构完全一样的数据表和物化视图。

每当物化视图刷新的时候,数据会同步到备库上的同名物理表。

好处：当删除物化视图的时候，备库上的物理表不会一并删除，并且保存数据。

使用方法：

SQL> create table a (col1 varchar2(20),col2 number);
Table created.
SQL> create table b (col1 varchar2(20),col2 number);
Table created.

SQL> alter table a add constraint pk_a primary key(col1);
Table altered.

SQL> insert into a values('1',1);
1 row created.
SQL> insert into a values('2',2);
1 row created.
SQL> commit;
Commit complete.

SQL> create materialized view log on a with primary key;
Materialized view log created.

SQL> create materialized view b on prebuilt table
2 as select * from a;
Materialized view created.
SQL> select object_name,object_type from user_objects
2 where object_name='B';
OBJECT_NAME
OBJECT_TYPE

---------------------------------------------- 
B TABLE

B
MATERIALIZED VIEW
2 rows selected.
SQL> select count(*) from b;
COUNT(*)

----------

0
1 row selected.
SQL> exec dbms_mview.refresh('b','c');
PL/SQL procedure successfully completed.
SQL> select * from b;
COL1 COL2

-------------------- ----------

1 1

2 2
2 rows selected.
SQL> insert into a values('3','3');
1 row created.
SQL> commit;
Commit complete.

SQL> select * from a;
COL1 COL2

-------------------- ----------

1 1

2 2

3 3
3 rows selected.
SQL> insert into a values('4',4);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh('b','f');
PL/SQL procedure successfully completed.
SQL> select * from b;
COL1 COL2

-------------------- ----------

1 1

2 2

3 3

4 4
4 rows selected.
SQL> select object_name,object_type from user_objects
2 where object_name='B';
OBJECT_NAME OBJECT_TYPE

------------------------------------------------------------ 
B TABLE

B MATERIALIZED VIEW
2 rows selected.
SQL> drop materialized view b;
Materialized view dropped.
SQL> select object_name,object_type from user_objects
2 where object_name='B';
OBJECT_NAME OBJECT_TYPE

------------------------------------------------------ 
B TABLE
1 row selected.
SQL> select * from b;
COL1 COL2

-------------------- ----------

1 1

2 2

3 3

4 4
4 rows selected.