实体化视图

Oracle 的实体化视图是包括一个查询结果的数据库对像，它是远程数据的本地副本，或者用来
生成基于数据表求和的汇总表。实体化视图存储基于远程表的数据，也可以称为快照。
实体化视图可用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，
就可以避免这些耗时的操作，而从快速地得到结果。实体化视图在很多方面和索引相类似：使用
实体化视图的目的是为了提高查询性能；实体化视图对应用透明，增加和删除实体化视图不会影
响应用程序中 SQL 语句的正确性和有效性；实体化视图需要占用存储空间；当基表发生变化时，
实体化视图也应当同步刷新。

创建实体化视图示例
建立完全更新的视图需要用户具有 connect, resource 和 create materialized view
权限，如果需要激活 query rewrite, 则还需要授予 global query writer 权限（或针对
涉及的表单独授权）

1. 以 dba 身份创建用户并进行授权：

SQL>conn / as sysdba 

SQL>grant connect to mtest;

SQL>grant resource to mtest;

SQL>grant create materialized view to mtest;

SQL>grant global query rewrile to mtest;

2. 创建表空间 MV_DATA 用于存放实体化视图和相关日志：

SQL>create tablespace MV_DATA datafile '+DATA' size 1G

    2   autoextend on next 2M maxsize 2G;

3. 建立测试用的基础表：

SQL>conn mtest/mtest

SQL>create table person(id int primary key, name varchar(50),

    2   deptid int not null);

SQL>create table dept(id int primary key, name varchar(50));

4. 插入测试数据：

SQL>insert into person (id, name, deptid) values(1, 'hr' , 100);

SQL>insert into person (id, name, deptid) values(2, 'scott', 101);

SQL>insert into dept (id, name) values(100, 'sales');

SQL>insert into dept (id, name) values(101, 'opert');

二、主键实体化视图
1. 创建 person 表的实体化视图日志：

SQL>CREATE MATERIALIZED VIEW LOG ON person;

2. 创建实体化视图，采用从当前开始的快速刷新方式，刷新间隔 1 分钟：

SQL>create materialized view mv_per_pk tablespace MV_DATA

    2   refresh fast start with sysdate

    3   next sysdate + 1/24/62

    4   with primary key

    5   as select * from person;

3. 查看实体化视图的数据：

SQL>select * from mv_per_pk;

4. 插入新的数据：

SQL>insert into person (id, name, deptid) values(3, 'tiger', 103);

SQL>commit;

5. 等待一分钟后查看实体化视图，新插入的数据已经刷新：

三、Rowid 实体化视图
1. 创建 Rowid 实体化视图，默认采用 ON DEMAND 刷新模式：

SQL>create materialized view mv_per_rowid tablespace MV_DATA

    2   REFRESH WITH ROWID

    3   AS

    4   SELECT * FROM person;

2. 查看实体化视图数据：

SQL>select * from mv_per_rowid;

3. 删除 person 的一条数据：

SQL>deleta person where id=3;

SQL>commit;

4. 查看结果：

SQL>select * from person;

SQL>select *from mv_per_rowid;

5. 手动刷新 mv_per_rowid 视图，然后查询结果：

SQL>exec dbms_mview.refresh('mv_per_rowid');

SQL>select * from person;

四、子查询实体化视图
1. 插入数据库：

SQL>insert into person (id, name, deptid) values(15, 'suci', 110);

SQL>insert into  person (id, name, deptid) values(2,  'david', 120);

SQL>commit;

2. 创建实体化视图：

SQL>CREATE MATERIALIZED VIEW mv_per_dept tablespace MV_DATA

     2   AS SELECT * FROM person e

     3   WHERE EXISTS

     4   (SELECT * FROM dept d

     5    WHERE e.deptid  = d.id);

3. 查询数据结果：

4. 插入数据后手动刷新视图检查数据：

SQL>inser into dept (id, name) values(120, 'Finance');

SQL>commit;

SQL> select * from dept;

SQL>select * from mv_per_dept;

SQL>exec dbms_mview.refresh('mv_per_dept');

五、关联查询实体化视图
1．快速刷新的关联查询实体化视图

创建快速刷新的关联查询实体化视图，首先对关联查询涉及的所有基表创建实体化视图日志，为
避免产生 ORA-12052: cannot fast refresh materialized view XXX 报错，实体化
视图日志要增加 ROWID 列

SQL>CREATE MATERIALIZED VIEW LOG ON dept with rowid;

SQL>ALTER MATERIALIZED VIEW LOG ON person add rowid;

SQL>CREATE MATERIALIZED VIEW mv_per_dept _name1 tablespce MV_DATA

    2   refresh fast start with sysdate next sysdate+1/24/60

    3   AS 

    4   SELECT  e.rowid "per_rowid", d.rowid "dept_rowid",

    5   e.*, d.NAME dept_name FROM person e, dept d

    6   WHERE e.deptid = d.id;

检查结果：

SQL>slect * from mv_per_dept_name1;

插入新的数据库后等待 1 分钟后检查实体化视图数据：

SQL>insert into person(id, name, deptid) values(8, 'tiya', 108);

SQL>insert into dept(id, name) values(  108, 'Executive');

SQL>commit;

SQL>select * from mv_per_dept_name1;

2．完全刷新的关联查询视图
删除基表的实体化视图日志：

SQL>drop MATERIALIZED VIEW LOG ON person;

SQL>drop MATERIALIZED VIEW mv_per_dept_name1;

创建 commit 提交时完全刷新的实体化视图：
SQL>CREATE MATERIALIZED VIEW mv_per_dept_name2

     2   refresh COMPLETE on commint

     3   AS SELECT e.*, d.NAME dept _name

     4   FROM person e, dept d

     5  WHERE e.depetid  = d.id;

SQL>col NMAE fromat a10

SQL>col DEPT_NAME format a20

SQL>seelct * from mv_per_dept_name2;

SQL>select * from mv_per_dept_name2;

SQL>delete person where id=8;

SQL>commit;

SQL>select * from mv_per_dept_name2;

六、刷新实体化视图的方法
 自动刷新方式
1. 创建实体化视图时使用 commit 选项；
2.创建实体化视图时使用 on demand start with … next …子句；
3.使用 dbms_mview 创建 job 定时刷新。
手动刷新方法
1.SQL> exec dbms_mview.refresh('mv_per_dept_name2', 'F');
-- 刷新指定的实体化视图，刷新类型有： C—完全刷新，F—快速刷新和强制刷新。
2.SQL> execute dbms_mview.refresh_defresh_dependent(‘EMPLOYEE’);
-- 刷新利用了该表的所有实体化视图。
3.SQL> execute dbms_mview.refresh_all_mviews;
-- 刷新该模式中自上次刷新以来，未得到刷新的所有实体化视图。