ORACLE物化视图测试
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。物化视图有ON DEMAND和ON COMMIT物化视图ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。ORACLE默认创建的物化视图是ON DEMAND 模式。
E:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 26 10:16:39 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/cat
已连接。
SQL> create materialized view mv_test as select * from dept1; --创建ON DEMAND 物化视图
实体化视图已创建。
SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST';
REFRES
------
DEMAND
SQL> analyze table mv_test compute statistics; --分析物化视图
表已分析。
SQL> analyze table dept1 compute statistics; --分析表
表已分析。
SQL> select t.TABLE_NAME,t.NUM_ROWS from user_tables t where t.TABLE_NAME in ('MV_TEST','DEPT1'); --查看当前基表和物化视图的行数
TABLE_NAME NUM_ROWS
------------------------------ ----------
DEPT1 6
MV_TEST 6
物化视图最重要的功能和特性之一,就是其数据会随着基表(或称主表,master表,本例中为TESTCF)的变化而变,基表数据增了,物化视图数据会变多;基表数据删了,物化视图数据也会变少。
但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式,手工刷新和自动刷新。默认是手工刷新,自动刷新可以通过JOB等方法实现
SQL> create view v_test as select * from dept1; --创建一个普通视图
视图已创建。
SQL> select * from v_test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
已选择6行。
SQL> insert into dept1(deptno,dname,loc) values (70,'test','test'); --插入一条测试数据
已创建 1 行。
SQL> select * from v_test; --普通视图数据已变化
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test
已选择7行。
SQL> select * from mv_test; --可以看见物化视图数据并没有变化
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
已选择6行。
创建ON COMMIT物化视图
SQL> insert into mv_test(deptno,dname,loc) values (70,'test','test'); --物化视图不能像普通视图表那样插入数据
insert into mv_test(deptno,dname,loc) values (70,'test','test')
*
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法
SQL> create materialized view mv_test1 refresh force on commit as select * from dept1;
create materialized view mv_test1 refresh force on commit as select * from dept1
*
第 1 行出现错误:
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性 - -此错误是由于没有创建物化视图日志
SQL> create materialized view log on dept1 ;
create materialized view log on dept1
*
第 1 行出现错误:
ORA-12014: 表 'DEPT1' 不包含主键约束条件
SQL> alter table dept1 add constraints pk_deptno primary key (deptno);
SQL> create materialized view log on dept1 ;
实体化视图日志已创建。
SQL> create materialized view mv_test1 refresh force on commit as select * from
dept1;
实体化视图已创建。
SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST1' --查看刷新模式
;
REFRES
------
COMMIT
测试ON COMMIT物化视图的更新特性
SQL> set timing on;
SQL> insert into dept1(deptno,dname,loc) values (80,'test','test');
已创建 1 行。
已用时间: 00: 00: 00.04
SQL> commit;
提交完成。
已用时间: 00: 00: 00.09
SQL> select * from mv_test1; --可以看到数据已经更新过来了,但是对COMMIT速度会有一些影响,越大的表影响越大。
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test
80 test test
已选择8行。
已用时间: 00: 00: 00.00
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。物化视图有ON DEMAND和ON COMMIT物化视图ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。ORACLE默认创建的物化视图是ON DEMAND 模式。
E:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 26 10:16:39 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/cat
已连接。
SQL> create materialized view mv_test as select * from dept1; --创建ON DEMAND 物化视图
实体化视图已创建。
SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST';
REFRES
------
DEMAND
SQL> analyze table mv_test compute statistics; --分析物化视图
表已分析。
SQL> analyze table dept1 compute statistics; --分析表
表已分析。
SQL> select t.TABLE_NAME,t.NUM_ROWS from user_tables t where t.TABLE_NAME in ('MV_TEST','DEPT1'); --查看当前基表和物化视图的行数
TABLE_NAME NUM_ROWS
------------------------------ ----------
DEPT1 6
MV_TEST 6
物化视图最重要的功能和特性之一,就是其数据会随着基表(或称主表,master表,本例中为TESTCF)的变化而变,基表数据增了,物化视图数据会变多;基表数据删了,物化视图数据也会变少。
但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式,手工刷新和自动刷新。默认是手工刷新,自动刷新可以通过JOB等方法实现
SQL> create view v_test as select * from dept1; --创建一个普通视图
视图已创建。
SQL> select * from v_test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
已选择6行。
SQL> insert into dept1(deptno,dname,loc) values (70,'test','test'); --插入一条测试数据
已创建 1 行。
SQL> select * from v_test; --普通视图数据已变化
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test
已选择7行。
SQL> select * from mv_test; --可以看见物化视图数据并没有变化
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
已选择6行。
创建ON COMMIT物化视图
SQL> insert into mv_test(deptno,dname,loc) values (70,'test','test'); --物化视图不能像普通视图表那样插入数据
insert into mv_test(deptno,dname,loc) values (70,'test','test')
*
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法
SQL> create materialized view mv_test1 refresh force on commit as select * from dept1;
create materialized view mv_test1 refresh force on commit as select * from dept1
*
第 1 行出现错误:
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性 - -此错误是由于没有创建物化视图日志
SQL> create materialized view log on dept1 ;
create materialized view log on dept1
*
第 1 行出现错误:
ORA-12014: 表 'DEPT1' 不包含主键约束条件
SQL> alter table dept1 add constraints pk_deptno primary key (deptno);
SQL> create materialized view log on dept1 ;
实体化视图日志已创建。
SQL> create materialized view mv_test1 refresh force on commit as select * from
dept1;
实体化视图已创建。
SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST1' --查看刷新模式
;
REFRES
------
COMMIT
测试ON COMMIT物化视图的更新特性
SQL> set timing on;
SQL> insert into dept1(deptno,dname,loc) values (80,'test','test');
已创建 1 行。
已用时间: 00: 00: 00.04
SQL> commit;
提交完成。
已用时间: 00: 00: 00.09
SQL> select * from mv_test1; --可以看到数据已经更新过来了,但是对COMMIT速度会有一些影响,越大的表影响越大。
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test
80 test test
已选择8行。
已用时间: 00: 00: 00.00
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28894640/viewspace-1061102/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28894640/viewspace-1061102/