oracle中普通视图和实体试图的区别

Oracle普通视图和实体化视图比较

来源：茂盛博客|2013-07-30

Oracle普通视图和实体化视图比较

相对于普通的视图来说，实体化视图的不同之处在于实体化视图管理存储数据，占据数据库的物理空间。实体化视图的结果会保存在一个普通的数据表中，在对实体化视图进行查询的时候不再会对创建实体化视图的基表进行查询，而是直接查询实体化视图对应的结果表，然后通过定期的刷新机制来更新MView表中的数据。 

首先我们需要创建表，然后写一个 SELECT 语句 。 

SQL> create table xjzhang_table1 (a varchar2(10),b number(10)); 

表已创建。 

SQL> create table xjzhang_table2 (a varchar2(10),b number(10)); 

表已创建。 

向两张表中插入数据 

SQL> insert into xjzhang_table1 values ('aaa','00001'); 

已创建 1 行。 

SQL> insert into xjzhang_table1 values ('bbb','00002'); 

已创建 1 行。 

SQL> insert into xjzhang_table2 values ('aa1','00002'); 

已创建 1 行。 

SQL> insert into xjzhang_table2 values ('bb1','00003'); 

已创建 1 行。 

SQL> commit; 

提交完成。 

然后我们创建一个视图，视图的名称为xjzhang_view 

SQL> create view xjzhang_view as select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b; 

视图已创建。 

然后我们查询视图 

SQL> select * from xjzhang_view; 

A          B 

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

bbb        2 

然后我们写一个 查询语句 

SQL> select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b; 

A          B 

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

bbb        2 

可以看到我们查询视图的结果和查询那个SELECT语句的结果是一致的，说明视图是查询一个 或者多个表的 SELECT 语句的描述。 

查询一下我们创建的视图 

select object_name,object_type,created,status from dba_objects where 

object_name='XJZHANG_VIEW'; 

OBJECT_NAME     OBJECT_TYPE         CREATED        STATUS 

--------------- ------------------- -------------- ------- XJZHANG_VIEW    VIEW                24-6月 -09     VALID 

同样我们先创建 一张表表名为 xjzhang_table3同时对表插入数据 

SQL> create table xjzhang_table3 (a varchar2(10),b number(5)); 

表已创建。 

SQL> insert into xjzhang_table3 values ('aaa','00001'); 

已创建 1 行。 

SQL> insert into xjzhang_table3 values ('bbb','00002'); 

已创建 1 行。 

SQL> commit; 

提交完成。 

下面我们开始创建实体视图 （这里 我们创建的实体视图 不是自动刷新 而是需要手动去刷新） 

SQL> create materialized view xjzhang_mat_view as select * from xjzhang_table3; 

实体化视图已创建。 

我们查询一下我们创建的实体视图，实体视图的名称为 xjzhang_mat_view 

SQL> select * from xjzhang_mat_view; 

A          B 

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

aaa        1 

bbb        2 

实体视图从某种意义上说是一张物理表可以通过 DBA_TABLES 进行查询来论证一下 

SQL>select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_MAT_VIEW'; 

TABLE_NAME                     TABLESPACE_NAME                STATUS 

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

XJZHANG_MAT_VIEW               SYSTEM                         VALID 

我们来查询一下刚才创建的 视图 xjzhang_view 

SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_VIEW'; 

未选定行 

可以看出普通视图在DBA_TABLES 中 是没有记录的，也没有对应的表空间 

实体视图会占用一定的存储空间，因为它存放了查询的结果集，那么它也是一种段，可以在DBA_SEGMENTS 中查询出 

SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_MAT_VIEW'; 

SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME 

-------------------- ------------------ ------------------------------XJZHANG_MAT_VIEW     TABLE              SYSTEM 

同样我们通过 DBA_SEGMENTS 来查询一下我们创建的普通视图 

SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_VIEW'; 

未选定行 

可以看出普通视图是不被记录在 DBA_SEGMENTS 中的。 

下面我们更新一下XJZHANG_TABLE3 表中的信息，看一下 实体视图的变化信息 

SQL> insert into xjzhang_table3 values ('ccc','00003'); 

已创建 1 行。 

SQL> commit; 

提交完成。 

查询该表的信息 

SQL> select * from xjzhang_table3; 

A          B 

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

aaa        1 

bbb        2 

ccc        3 

表的记录增加了一行 

我们再来查询实体视图的信息 

SQL> select * from xjzhang_mat_view; 

A          B 

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

aaa        1 

bbb        2 

可以看出实体视图的信息没有发生变化，因为我们在创建实体视图的时候，我们没有指定该视图的刷新方法和刷新模式，所以创建完该实体视图，该视图默认的刷新方法和刷新模式为 FORCE DEMAND 

我们可以通过 dba_mviews 这个视图查询我们创建的实体视图的信息 

SQL> select a.mview_name,a.refresh_mode,a.refresh_method from dba_mviews a where a.mview_name='XJZHANG_MAT_VIEW'; 

MVIEW_NAME                     REFRESH_MODE   REFRESH_METHOD 

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

XJZHANG_MAT_VIEW               DEMAND         FORCE 

这里默认的是手工刷新,所以在这里我们对实体视图进行更新 

SQL> EXEC DBMS_MVIEW.REFRESH('XJZHANG_MAT_VIEW') 

PL/SQL 过程已成功完成。 

然后我们再次查询该实体视图 

SQL> SELECT * FROM XJZHANG_MAT_VIEW; 

A          B 

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

aaa        1 

bbb        2 

ccc        3 

这说明了基表的数据发生变化，那么实体视图的内容也将被写入到对应的存储空间中。 

我们也可以创建自动更新的实体视图,同样我们创建一张表 

SQL> create table xjzhang_table4 (a varchar2(10),b number(5)); 

表已创建。 

SQL> insert into xjzhang_table4 values ('aaa','00001'); 

已创建 1 行。 

SQL> commit; 

提交完成。 

然后我们创建实体视图 

SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4; 

create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4 

* 

第 1 行出现错误: ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性 

物化视图的快速刷新采用了增量的机制，在刷新时，只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。但是快速刷新具有较多的约束，而且对于采用ON COMMIT模式进行快速刷新的物化视图更是如此。对于包含聚集和包含连接的物化视图的快速刷新机制并不相同，而且对于多层嵌套的物化视图的快速刷新更是有额外的要求。如此多的限制一般很难记全，当建立物化视图失败时，Oracle给出的错误信息又过于简单，有时无法使你准确定位到问题的原因。Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因。 

Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因,我们来试一下ORACLE 提供的包. 

使用EXPLAIN_MVIEW过程首先要建立MV_CAPABILITIES_TABLE表 

创建步骤如下 

我们执行一个脚本来完成创建 

SQL> @?/RDBMS/ADMIN/utlxmv.sql 

表已创建。 

然后我们执行这个包 

SQL> begin 

2 dbms_mview.explain_mview('select * from xjzhang_table4'); 

3 end; 

4 

/ 

PL/SQL 过程已成功完成。 

然后我们通过 select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%'这个脚本来查询结果 

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME                 P MSGTXT 

------------------------------  - ------------------------------ REFRESH_COMPLETE                N 主表中没有任何主键约束条件 REFRESH_FAST                    N 

REFRESH_FAST_AFTER_INSERT       N 详细信息表没有实体化视图日志 

REFRESH_FAST_AFTER_ONETAB_DML   N 查看禁用 REFRESH_FAST_AFTER_IN SERT 的原因 

REFRESH_FAST_AFTER_ANY_DML      N 查看禁用 REFRESH_FAST_AFTER_ON ETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 

已选择6行。 

我们可以看到第一条 

REFRESH_COMPLETE N 主表中没有任何主键约束条件 

我们给 xjzhang_table4 创建主键 

SQL> alter table xjzhang_table4 add (constraint xjzhang_pri primary key (b)); 

表已更改。 

然后我们再次创建实体视图 

SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4; 

实体化视图已创建。 

可以看已经成功创建,我们现在向表中插入数据来查看实体视图的变化情况 

我们首先查询一下表中的记录和实体视图中的记录 

SQL> select * from xjzhang_table4; 

A          B 

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

aaa        1 

SQL> select * from xjzhang_mat_view1;  

A          B 

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

aaa        1 

然后我们向表中插入 一条记录 

SQL> insert into xjzhang_table4 values ('afd','00002'); 

已创建 1 行。 

SQL> commit; 

提交完成。 

我们再来查询一下实体视图的内容 

SQL> select * from xjzhang_mat_view1; 

A          B 

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

aaa        1 

物化视图为什么没有变化 

刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVE*。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND 

通过上面的这段话我们知道,刷新的类型 一般有两种：ON DEMAND 和 ON COMMIT 

ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新,这种方法也就是我们长说的使用手工刷新,而ON COMMIT也就是我们长说的 自动刷新,而刷新的方法有四种FAST、COMPLETE、FORCE和NEVER 

我们选择的是FORCE 说明ORACLE 是有选择性的刷新,如果可以采用FAST,要不才用COMPLETE 

我们采用手工刷新 

EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C'); 

C 代表 完全刷新 

F 代表 快速刷新和强制刷新 

SQL> select * from xjzhang_mat_view1; 

A          B 

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

aaa        1 

SQL> EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C'); 

PL/SQL 过程已成功完成。 

SQL> select * from xjzhang_mat_view1; 

A          B 

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

aaa        1 

afd        2 

我们删除该实体视图 

SQL> drop materialized view xjzhang_mat_view1; 

实体化视图已删除。 

然后我们重新创建实体视图采用 FAST 方法 

SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; 

create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 

* 

第 1 行出现错误: ORA-23413: 表 "SYS"."XJZHANG_TABLE4" 不带实体化视图日志 

错误提示需要带实体化视图日志 

我们来创建实体化视图日志 

SQL> create materialized view log on xjzhang_table4 with rowid, sequence (a, b) including new values; 

实体化视图日志已创建。 

然后我们再次创建实体视图 FAST 方法 

SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; 

create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 

* 

第 1 行出现错误: ORA-23415: "SYS"."XJZHANG_TABLE4" 的实体化视图日志不记录主键 

又提示错误，根据提示错误我们主键失效 

SQL> alter table xjzhang_table4 modify constraint xjzhang_pri disable; 

表已更改。 

然后我们再次创建物化视图 FAST 

SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; 

create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 

* 

第 1 行出现错误: ORA-12014: 表 'XJZHANG_TABLE4' 不包含主键约束条件 

这次系统又提示不包含主键约束条件，我们删除实体视图对应的日志 

SQL> DROP MATERIALIZED VIEW LOG ON xjzhang_table4; 

实体化视图日志已删除。 

我们在创建实体视图日志的时候设定主键 

SQL> create MATERIALIZED VIEW LOG ON xjzhang_table4 WITH PRIMARY KEY; 实体化视图日志已创建。 

然后我们再次创建实体视图 

SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; 

实体化视图已创建。 

可以看出创建成功，如果需要自动更新的话，我们需要创建一个自动执行的 JOB。