物化视图

oracle230

---

---

物化视图

历史：8i和8i之前，oracle提供了快照snapshot；在9i中改名为物化视图也叫实体化视图(materialized view).

作用：提高了查询速度，数据库性能调整的重点。

1. 调整sga和pga

2. 调整sql语句，使用绑定变量

3. 使用物化视图

4. 系统结构上，使用读写分离

1.读库、写库进行实时数据更新同步：

a. 在写库上使用logmnr实现日志挖掘，传输到读库上，重做日志

b. 在读库、写库之间使用queue

c. 11G中提供了DG技术

数据库的复制技术：物化视图、DG、消息队列。

视图：数据不存在；物化视图：数据实际存在其中的。

---

问题的提出：

在分布式的海量数据环境中，信息查询的速度问题显得尤为重要。传统的查询方式，即根据用户的要求，

每次都重新的进入基表或视图查询，所需的时间太长。例如移动通信行业，即使客户需要查询很少的信息，也可能会花费很多时间，

可能在30分钟左右，如果数据库主机稍有繁忙，这个时间会更长，客户难以忍受。为了解决这种问题，

ORACLE中设计了物化视图（又称为MV）。

简介：

物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和

的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以查询表，视图和其它的物化视图。

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

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须

用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

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

物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增

加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生

变化时，物化视图也应当刷新。

作用：

1.物化视图和表一样可以直接进行查询。物化视图可以基于分区表，物化视图本身也可以分区。

2. 数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，

在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。 在数据仓库中，还经常使用查询重写（query rewrite）机制，

这样不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。

3. 除了在数据仓库中使用，物化视图还用于复制、移动计算等方面。

创建物化视图需要的权限：

1.如果创建基于主键的物化视图，则必须具有访问主表、访问主表的日志、create MATERIALIZED VIEW这三个权限。

2.如果创建基于rowid的物化视图，则必须具有访问主表、create MATERIALIZED VIEW这两个权限。

创建物化视图的选项：

1.查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。

分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，

Oracle会自动判断能否通过查询物化视图来得到结果。默认为DISABLE QUERY REWRITE。

2. 物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，

可以建立为ROWID或PRIMARY KEY类型的。

3. 刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。

刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，

可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。

ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。

FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的

刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，

否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。

基于rowid的物化视图：

1.使用条件：远程表可以没有主键。

2. 远端操作：

创建表。

3. 本地操作：创建一个过程，实现刷新本地物化视图的动作；创建一个JOB，进行调用过程的操作；运行这个JOB，则本地数据会与远程数据自动同步刷新一次。

这个是通过rowid来建立的，虽然没有主键但是rowid在表中的行是唯一的 SQL> show user USER is "HR" SQL> create table student(id number ,name varchar2(20)); Table created. SQL> grant select on student to test1; Grant succeeded. SQL>

create materialized view student_view refresh with rowid as select * from hr.student createorreplaceprocedurestudent_proas begin dbms_refresh.refresh('STUDENT_VIEW'); end; variablejob_testnumber; begin dbms_job.submit(:job_test,'student_pro;',sysdate,'sysdate+1/1440'); end; begin dbms_job.run(:job_test); end; begin dbms_job.remove(:job_test); end;

基于主键的物化视图：

1.使用条件：远程表必须包含主键。

2.远端操作：

创建主键表，并创建基于主表的视图日志。
3.本地操作：创建一个过程，实现刷新本地物化视图的动作；创建一个JOB，进行调用过程的操作；

运行这个JOB，则本地数据会与远程数据自动同步刷新一次。

--这是通过定义物化视图时定义刷新 SQL> conn test1/test1 Connected. SQL> !clear SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$DXVOZq/gAwngUKjAA1tAGQ==$0 TABLE CLUSTER1 CLUSTER COUNTRIES TABLE DEPARTMENTS TABLE DEPT TABLE DROPPED_OBJ TABLE EMPLOYEES TABLE JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE REGIONS TABLE 11 rows selected. SQL> purge recyclebin; Recyclebin purged. SQL> create table student(id number primary key ,name varchar2(20)); Table created.1.--创建表 SQL> create materialized view log on student; Materialized view log created.2. --创建物化视图日志 SQL> SQL> SQL> grant select on student to hr;4. --赋予访问表的权限 Grant succeeded. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- CLUSTER1 CLUSTER COUNTRIES TABLE DEPARTMENTS TABLE DEPT TABLE DROPPED_OBJ TABLE EMPLOYEES TABLE JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE MLOG$_STUDENT TABLE--作用是记录基表的日志 REGIONS TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- RUPD$_STUDENT TABLE--说明该表的dml操作会在物化视图中同步 STUDENT TABLE 13 rows selected. SQL> grant select on MLOG$_STUDENT to hr;--赋予访问日志的权限 Grant succeeded. SQL> insert into student values(1,'zhangsan');7. --插入数据 1 row created. SQL> commit;8. --提交 Commit complete. SQL> SQL> drop table student purge; Table dropped. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- CLUSTER1 CLUSTER COUNTRIES TABLE DEPARTMENTS TABLE DEPT TABLE DROPPED_OBJ TABLE EMPLOYEES TABLE JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE REGIONS TABLE 10 rows selected. --删除基表随带的两个表也被删除掉了 SQL>

SQL> conn hr/hr Connected. SQL> create materialized view student_view 2 refresh fast 3 start with sysdate 4 next sysdate + 1/1440 5 with primary key 6 as 7 select * from test1.student; select * from test1.student * ERROR at line 7: ORA-00942: table or view does not exist --3. 上面说明没有访问表权限 SQL> select * from test1.student; no rows selected5. --可以访问表 SQL> create materialized view student_view 2 refresh fast 3 start with sysdate 4 next sysdate + 1/1440 5 with primary key 6 as 7 select * from test1.student; Materialized view created. 6. --没有创建物化视图权限，通过sys用户赋予权限 SQL> select * from student_view; no rows selected --没有记录 SQL> select * from student_view; ID NAME ---------- -------------------- 1 zhangsan --一分钟之后查询到了记录信息 SQL>

--这是通过job来进行的刷新的 SQL> create table student (id number primary key ,name varchar2(20)); Table created. SQL> create materialized view log on student; Materialized view log created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- CLUSTER1 CLUSTER COUNTRIES TABLE DEPARTMENTS TABLE DEPT TABLE DROPPED_OBJ TABLE EMPLOYEES TABLE JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE MLOG$_STUDENT TABLE REGIONS TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- RUPD$_STUDENT TABLE STUDENT TABLE 13 rows selected. SQL> grant select on student to hr; Grant succeeded. SQL> grant select on MLOG$_STUDENT to hr; Grant succeeded. SQL> insert into student values(1,'zhangsan'); 1 row created. SQL>

SQL> show user USER is "HR" SQL> SQL> create materialized view student_view as select * from test1.student; Materialized view created. SQL> create or replace procedure student_pro as begin dbms_mview.refresh('STUDENT_VIEW'); end; 2 3 4 5 6 / Procedure created. SQL> begin dbms_job.submit(:job_test,'student_pro;',sysdate,'sysdate+1/1440'); end; 2 3 4 / PL/SQL procedure successfully completed. SQL> begin dbms_job.run(:job_test); end; 2 3 4 / PL/SQL procedure successfully completed. SQL> select * from student_view; ID NAME ---------- -------------------- 1 zhangsan SQL>

使用物化视图和exp实现生产库的逻辑备份的例子

问题：

在备份库上对应于生产库的重要的表（需要实时备份的）建立物化视图，按照不同的要求制定刷新时间，

开始任务；在生产库上使用exp进行对于用户的逻辑备份，每天2：00进行，同时把3天前的逻辑备份删除