ORACLE中的物化视图（OCM复习总结）

1、  基本概念

视图是一个虚拟表，基于它创建时指定的查询语句返回结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询的结果集存储到一个物化视图。也就是说，物化视图只是对已经存储于别处的数据的转换和复制。

        执行创建物化视图的语句实际上会创建一个物化视图和一个容器表，容器表是一个普通的表，它与物化视图拥有同样的名称，并且这个表可以像任何其他表一样被查询。执行计划中的MAT_VIEW_ACCESS_FULL表明使用了物化视图，直接使用容器表所对应的语句是一种方式，而另外一种方式就是查询重写。

        查询重写也就是当查询优化器收到一条待优化的查询，可以选择物化视图来对它进行重写，只要这个物化视图包含查询的全部和部分数据。可以使用HINT REWRITE和NO_REWRITE来影响优化器的决定。

        要执行查询重写必须设置参数query_rewirte_enabled设置为TRUE，其次还必须对这个物化视图启用查询重写。

         Alter materialized view ** enable query rewrite

2、  参数

        语法：

        Create materialized view **

{TABLESPACE ts_name}

        Refresh fast/complete/force with rowid/primary key on commit/demand

        {on demand start with syadate next sysdate+1}

        { NEVER REFRESH }

        Enable query rewirte

        Build immediate/deferred

        {Using on index}

        As

        *************;

       create materialized view log on t_rowid with rowid, sequence (name, num) including new values;

       --可以确定容器表的物理属性

       --创建物化视图时，查询语句会立即把查询的数据插入到容器表中，这是因为build immediate是默认的选项，其他选项是build deferred来制定插入记录的时间延迟到第一次进行刷新的时候，还有一个选项是on perbuild table 来使用一个已经存在的表作为容器表

       --查询重写功能在默认情况下并不启动，要启动需要设定enable query rewrite

       --默认情况下，为了提高fast refresh的性能会自动为容器表创建一个索引，如果不想使用可以制定using no index来实现。

       --刷新依赖方式，可以是PRIMARY KEY也可以是ROWID。当使用的时FAST刷新方式的时候，物化视图日志也需要建立为相应的ROWID或者PRIMARY KEY。

3、查询重写

       --全文匹配的查询重写：查询优化器对传递来的查询语句的文本与每个存在的物化视图文本进行比较，不区分大小写，但是字面量除外。

       --部分文本匹配的查询重写：虽然语句和物化视图语句有部分差异，但是物化视图包含了查询所需要的所有信息

       --一般查询重写：通过应用约束和维度信息来判断基础表之间的关系，目的在于即使查询语句和物化视图语句有较大的差异也能应用物化视图，一般情况下优化器不会使用未经验证的约束，在这样的情况下需要使用参数query_rewrite_integrity来指定一般查询重写可以利用未经验证的约束。参数设置选项如下：

         Enforced：只有物化视图是最新的数据使用查询重写，另外只有通过验证的约束才能被一般查询重写使用。

         Trusted：只有物化视图是最新的数据，另外维度和约束信息在未验证（NOVALIDATE）的情况下并且设置为rely也能使用一般查询重写

         Stale_tolerated：包含失效的数据的物化视图也能被重写，另外维度和约束信息在未验证（NOVALIDATE）的情况下并且设置为rely也能使用一般查询重写

4、  刷新

     当表被修改的时候，依赖它的物化视图也会失效。因此，需要执行一次刷新来保持物化视图的有效性，在创建物化视图的时候，可以指定如何以及何时进行刷新操作，方法如下:

完全刷新—容器表中的所有内容都被删除，然后重基表进行加载。一般要由大量数据被修改才使用这种方式。

快速刷新---容器表中的内容会被重用，只有被修改的数据才会被同步到容器表。如果基表只有少量数据被修改，使用这样的方式。

强制刷新---先会尝试快速刷新,如果无法刷新就尝试完全刷新。

永不刷新---物化视图永远不刷新。

 

可以指定以下两种方式来指定物化视图的刷新的时间点

ON DEMAND---物化视图会在请求的情况下进行刷新，这意味着从基础表修改到物化视图刷新这段时间可能有数据丢失。

ON COMMIT---物化视图会在基础表修改所有在的同一个事物中进行自动刷新，就回话而言数据总是最新的。

手动刷新---使用DBMS_view中的以下过程来完成：

Refresh—这个存储过程用于刷新单个或多个物化视图，LIST用于列出名字，多个物化视图用逗号隔开

dbms_mview.refresh_all_view(list => ‘xxx,bbbb’)

refresh_all_mviews---这个存储过程刷新所有的物化视图，NUMBER_OF_FAILURES用于表示刷新时的错误

dbms_mview.refresh_all_mviews(number_of_failures =>:r)

refresh_dependent---这个存储过程用于刷新依赖于基础表的物化视图，这些表通过list也传入到列表。NUMBER_OF_FAILURES用于表示刷新时的错误次数

所有存储过程都支持method和atomic_refresh，method可以指定为c全刷新，f快速刷新，？强制刷新。如果aotmic_refresh设置为false，则表示不使用单一事物，在这个时候完全刷新会使用truncate而不是delete。

    

        如果想根据时间来自动刷新，可以使用on demand start with ** next ** 来进行控制。

    Alter materialized view ** refresh complete on demand start with sysdate next sysdate+todsinterval(‘0 00:10:00’)，系统会自动提交一个JOB来完成这个任务。                    

 

5、  基于物化视图日志的快速刷新

在快速刷新过程中基础表上只要发生改变才会同步到容器表上，显然只有数据引擎知道基础表发生了那些变化才能进行，所以必须建立一个物化视图日志（materialized view log）物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型，同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。
任何物化视图都会包括的列：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
CHANGE_VECTOR$$表示修改矢量，用来表示被修改的是哪个或哪几个字段。如果WITH后面跟了ROWID，则物化视图日志中会包含：
M_ROW$$：用来存储发生变化的记录的ROWID。如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：SYS_NC_OID$：用来记录每个变化对象的对象ID。如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：
SEQUENCE$$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。

6、  实验

SQL> create table lll

  2  as

  3  select * from dba_users;

SQL> create materialized view log on lll with rowid;

Materialized view log created.

SQL> create materialized view test_ll_view

  2  refresh fast with rowid

  3  as

  4  select * from lll;

Materialized view created.

SQL> drop  materialized view log on lll;

 

Materialized view log dropped.

SQL> desc MLOG$_LLL

 Name                                      Null?    Type

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

 M_ROW$$                                            VARCHAR2(255)

 SNAPTIME$$                                         DATE

 DMLTYPE$$                                          VARCHAR2(1)

 OLD_NEW$$                                          VARCHAR2(1)

 CHANGE_VECTOR$$                                    RAW(255)

SQL>  create materialized view test_ll_view_1

  2    refresh fast with rowid on commit

  3     as

  4     select * from lll;

Materialized view created

SQL> create materialized view log on lll with rowid, sequence ( USER_ID) including new values ;

 

Materialized view log created.

SQL> desc MLOG$_LLL

 Name                                                  Null?    Type

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

 USER_ID                                                        NUMBER

 M_ROW$$                                                        VARCHAR2(255)

 SEQUENCE$$                                                     NUMBER

 SNAPTIME$$                                                     DATE

 DMLTYPE$$                                                     VARCHAR2(1)

 OLD_NEW$$                                                     VARCHAR2(1)

 CHANGE_VECTOR$$                                                RAW(255)

  基于时间

 

SQL> create materialized view test_mv_1_123

  2  refresh force with rowid on demand

  3   start with sysdate next sysdate+1

  4  as

  5  select * from test_mv_row;

7、  物化视图中的表和索引

---物化视图首先会包含一个容器表和物化视图的名字相同。

---如果建立了事物化视图日志，就多了一个日志表，日志表的名字为mlog$_tablename

---一般情况下在快速刷新的情况下，会 为容器表建立一个索引来加速快速刷新。基于ROWID的会建立在基表的M_ROW$$列上，而基于primary key 的会建立在primary key上。

实验：

 

SQL> create table test_mv

  2  as

  3  select * from dba_users;

 

Table created.

 

SQL> create table test_mv_row

  2  as

  3  select * from dba_users;

Table created.

SQL>  alter table test_mv

  2  add constraint test_mv_pk   primary key(username);

 

Table altered.

 

SQL> create materialized view log on test_mv with primary key;

 

Materialized view log created.

 

SQL> create materialized view log on test_mv_row with rowid;

 

Materialized view log created.

SQL> create materialized view test_mv_view

  2   refresh fast with primary key on commit

  3  as

  4  select * from test_mv;

 

Materialized view created.

SQL> create materialized view test_mv_1_view

  2  refresh fast with rowid on commit

  3   as

  4   select * from test_mv_row;

 

Materialized view created.

 

 

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME

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

TEST_MV_PK                     TEST_MV                        USERNAME

TEST_MV_PK1                    TEST_MV_VIEW                   USERNAME

I_SNAP$_TEST_MV_1_VIEW         TEST_MV_1_VIEW                 M_ROW$$

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

转载于:http://blog.itpub.net/7728585/viewspace-710167/