物化视图学习笔记

物化视图学习笔记

物化视图和视图从字面上来看就差两个字，可是差两个字就复杂很多了，

所以打算整理下自己物化视图方面的知识，让自己加深对物化视图的理解。

什么是物化视图及物化视图的特性

物化视图（materialized view）是一个schema 对象。可以用于汇总，计算，

复制，分发数据。物化视图中存储了SQL 语句的查询结果。

1、物化视图与视图一个很重要的不同在于物化视图是实际的存储SQL语句的结果，

需要消耗相应的存储空间，所以创建物化视图的用户需要有足够的表空间配额。

2、物化视图可以根据一定的策略在master table 改变以后refresh。

3、如果一个SQL 查询可以重写到一个物化视图，物化视图可以提高查询语句的性能。

4、物化视图对运用程序和用户是透明的。

物化视图使用的场景

1、在数据仓库环境下，可以使用物化视图存储来自于聚集函数的计算结果。比如sum,

avg.这样通过对物化视图的查询，可以提高响应时间，因为物化视图中的数据是预先

计算好的，或者是查询物化下来的join 结果集。

2、物化视图可以用在复制，所以物化视图时常被称作快照（snapshot）。

oracle 8i开始snapshot 被重新定义为物化视图，在语法方面也做了改动。

通过database link 可以把远程的master database 中的master tables 中的数据

的一部分或者全部复制到本地，这很适合于客户端不经常连接到网络中的情形

，方便本地访问数据，并且本地所在的更新也可以同步到master database。

3、在移动计算环境下，可以通过物化视图，定期的从中央服务器下载数据

到移动客户端，并上传客户端所在的更新。

我觉得后面两种情形都是复制特性的运用。

创建物化视图前的准备工作

1、在自己的schema 下创建物化视图。你需要获得create materialized view、

create table 系统权限。所有master table 的select 权限。

2、在其他用户的schema 下创建物化视图。你需要create any materialized view

系统权限。物化视图的所有者需要具备create table 系统权限和所有master table 

的select 权限。

注:

1、创建物化视图所需要的权限不能通过role 授权给user，而是要直接的授权给user.

2、为了能够创建refresh-on-commit 的物化视图，用户必须获得所有master table 的

on commit refresh 对象权限。

3、物化视图的所有者应该具备在相应表空间上足够的空间配额，以便存储物化视图。

4、创建可以被query rewrite 的物化视图，还需要额外的权限，请参考：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

*******************************************************************

关于创建物化视图权限的示例。

SQL> conn hr/hr
Connected.

SQL> create materialized view emp_mv
  2  refresh fast next sysdate
  3  as select employee_id,last_name,first_name
  4  from employees;
from employees
     *
ERROR at line 4:
ORA-01031: insufficient privileges

SQL> show user
USER is "SYS"

SQL> grant create materialized view to hr;
Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> create materialized view emp_mv
  2  refresh fast next sysdate
  3  as select employee_id,last_name,first_name
  4  from employees;
Materialized view created.

*****************************************************************

物化视图refresh 的方式

当master table 更新以后，可以通过以下两种方式来refresh 物化视图。

1、complete refresh 

这种方式的refresh 需要在定义物化视图的时候通过build immediate 指定。

这种方式的refresh 实际上是重新执行一遍该物化视图的定义的查询。

当master table 很大的时候，因为需要访问很多的大量的实际，所有速度

将会变的很慢。

2、fast refresh 

这种方式的refresh 不需要从头开始构建物化视图，而是通过增量的方式，

仅仅运用refresh 改变的那一部分，所有效率非常的高。为了支持fast refresh

需要创建materialized view logs 用来跟踪改变(insert,update,delete)。这个

很想redis 里面的aof 文件。materialized view logs 和master table 是在

同一个schema 中的。

***************************************************************
关于refresh的示例。

SQL> create materialized view log on employees;--先要创建物化视图log。
Materialized view log created.

SQL>  create materialized view emp_mv--创建一个物化视图指定refresh 的方式和时间。
  2   refresh fast next sysdate
  3   as select employee_id,last_name,first_name
  4   from employees;
Materialized view created.

SQL>  select * from emp_mv
  2   where employee_id = 100;
EMPLOYEE_ID LAST_NAME  FIRST_NAME
----------- ---------- ----------
         100 King       Steven

SQL> update employees--对master table 做一个更新。
  2     set last_name = upper(last_name)
  3     where employee_id = 100;
1 row updated.

SQL> select * from emp_mv--未提交的时候物化视图和master table 不同步。
  2  where employee_id = 100;
EMPLOYEE_ID LAST_NAME  FIRST_NAME
----------- ---------- ----------
         100 King       Steven

SQL> commit;
Commit complete.
SQL> select * from emp_mv--物化视图和master table 同步。
  2  where employee_id = 100;
EMPLOYEE_ID LAST_NAME  FIRST_NAME
----------- ---------- ----------
         100 KING       Steven

上面创建的物化视图指定的refresh 方式是fast refresh,时间是实时的同步。

下面创建一个refresh fast on commit 方式的物化视图.

我们已经在master table 上创建过materialized view log所以不需要再创建。

SQL>  create materialized view  emp_mv1
  2    refresh fast on commit
  3   as select employee_id,last_name,first_name
  4   from employees;

Materialized view created.
SQL> select * from emp_mv1
  2  where employee_id = 100;
EMPLOYEE_ID LAST_NAME      FIRST_NAME
----------- -------------- --------------
        100 King           Steven

在master table 中执行一个更新。
SQL> update employees
  2  set last_name = upper(last_name)
  3  where employee_id = 100;
1 row updated.

没有提交前查看下emp_mv1 。
SQL>  select * from emp_mv1
  2   where employee_id = 100;
EMPLOYEE_ID LAST_NAME      FIRST_NAME
----------- -------------- --------------
        100 King           Steven

提交以后会马上以fast refresh 方式刷新物化视图。
SQL> commit;
Commit complete.
SQL>  select * from emp_mv1
  2   where employee_id = 100;
EMPLOYEE_ID LAST_NAME      FIRST_NAME
----------- -------------- --------------
         100 KING           Steven

*****************************************************************

关于物化视图自动query rewrite 的示例。

这里说的是自动查询重写，而不是在from 子句中指定查询的物化视图。query rewrite是优化器的一项功能，优化器将会在语义一致的前提下使用带有物化视图的执行计划。

SQL> set autotrace trace explain
SQL> select employee_id,last_name,first_name
  2  from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   108 |  2052 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   108 |  2052 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

不重写，why？因为我们创建emp_mv1 的时候没有指定enable query rewrite.可以通过下面的语句实现enable query rewrite 。

SQL> alter materialized view emp_mv1 enable query rewrite;
Materialized view altered.

SQL> exec dbms_stats.gather_table_stats(user,'EMP_MV1');
PL/SQL procedure successfully completed.
SQL> SET LINES 120
SQL> select employee_id,last_name,first_name
  2  from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2814234707
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   108 |  1944 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_MV1 |   108 |  1944 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

当我们开启enable query write 的时候，我们发现对employees 的查询自动的重写为对物化视emp_mv1 的查询。

********************************************************************************

删除物化视图及物化视图logs 

SQL> drop materialized view emp_mv1;
Materialized view dropped.

SQL> drop materialized view log on employees;
Materialized view log dropped.

注：一个master table 上面只能创建一个materialized view log 对象。

小结：

整理了这些物化视图的知识，虽然脑袋知识条理清楚了很多。但是还有很多关于物化视图方面的知识需要学习，加油！

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

转载于:http://blog.itpub.net/26110315/viewspace-732511/