[20121101]物化视图与表(Materialized Views and Tables).txt
1.建立测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> grant dba to test identified by xxxxxx;
Grant succeeded.
SQL> create table deptx as select * from scott.dept ;
Table created.
2.测试建立物化视图1:
connect test/xxxxxx;
create materialized view mv_deptx as select count(*) from deptx;
SQL> create materialized view mv_deptx as select count(*) from deptx;
Materialized view created.
SQL> desc mv_deptx
Name Null? Type
--------- -------- -------
COUNT(*) NUMBER
--字段名使用count(*)不好.
SQL> drop materialized view mv_deptx;
Materialized view dropped.
SQL> create materialized view mv_deptx as select count(*) dept_count from deptx;
Materialized view created.
SQL> select * from user_objects where object_type in ('TABLE','MATERIALIZED VIEW');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
-------------------- -------------------- --------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ----------
MV_DEPTX 117700 MATERIALIZED VIEW 2012-11-01 17:10:28 2012-11-01 17:10:28 2012-11-01:17:10:28 VALID N N N 19
DEPTX 117696 117696 TABLE 2012-11-01 17:07:07 2012-11-01 17:07:07 2012-11-01:17:07:07 VALID N N N 1
MV_DEPTX 117699 117699 TABLE 2012-11-01 17:10:28 2012-11-01 17:10:28 2012-11-01:17:10:28 VALID N N N 1
--可以发现建立物化视图的同时,会建立相同名字的表.
SQL> drop materialized view mv_deptx;
Materialized view dropped.
SQL> select * from user_objects where object_type in ('TABLE','MATERIALIZED VIEW');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
-------------------- -------------------- --------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ----------
DEPTX 117696 117696 TABLE 2012-11-01 17:07:07 2012-11-01 17:07:07 2012-11-01:17:07:07 VALID N N N 1
--如果删除物化视图,对应的表也同时删除.
3.测试建立物化视图2:
SQL> create materialized view mv_deptx on prebuilt table as select count(*) dept_count from deptx;
create materialized view mv_deptx on prebuilt table as select count(*) dept_count from deptx
*
ERROR at line 1:
ORA-12059: prebuilt table "TEST"."MV_DEPTX" does not exist
--如果采用on prebuilt table模式,必须先建立表.
SQL> create table mv_deptx(a number);
Table created.
SQL> create materialized view mv_deptx as select count(*) dept_count from deptx;
create materialized view mv_deptx as select count(*) dept_count from deptx
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
--如果存在对应的表,建立时必须加入 on prebuilt table 参数.
SQL> create materialized view mv_deptx on prebuilt table as select count(*) dept_count from deptx;
create materialized view mv_deptx on prebuilt table as select count(*) dept_count from deptx
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
--可以发现字段命令无匹配,无法建立.
SQL> ALTER TABLE TEST.MV_DEPTX RENAME COLUMN A TO dept_count;
Table altered.
SQL> create materialized view mv_deptx on prebuilt table as select count(*) dept_count from deptx;
Materialized view created.
SQL> select * from user_objects where object_type in ('TABLE','MATERIALIZED VIEW');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
-------------------- -------------------- --------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ----------
MV_DEPTX 117701 117701 TABLE 2012-11-01 17:18:41 2012-11-01 17:20:32 2012-11-01:17:20:32 VALID N N N 1
DEPTX 117696 117696 TABLE 2012-11-01 17:07:07 2012-11-01 17:07:07 2012-11-01:17:07:07 VALID N N N 1
MV_DEPTX 117702 MATERIALIZED VIEW 2012-11-01 17:20:51 2012-11-01 17:20:51 2012-11-01:17:20:51 VALID N N N 19
--看看这个时候是否可以删除表?
SQL> drop table mv_deptx;
drop table mv_deptx
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "TEST"."MV_DEPTX"
--可以发现无法删除.
SQL> drop materialized view mv_deptx;
Materialized view dropped.
SQL> select * from user_objects where object_type in ('TABLE','MATERIALIZED VIEW');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
-------------------- -------------------- --------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ----------
MV_DEPTX 117701 117701 TABLE 2012-11-01 17:18:41 2012-11-01 17:20:32 2012-11-01:17:20:32 VALID N N N 1
DEPTX 117696 117696 TABLE 2012-11-01 17:07:07 2012-11-01 17:07:07 2012-11-01:17:07:07 VALID N N N 1
--可以发现删除物化视图,表依旧存在.
总结:
第1种方式建立物化视图,建立时同时建立表和视图.删除物化视图,同时消失.
第2种方式建立物化视图(on prebuilt table),建立物化视图前,先要建立表,才能物化视图时. 如果删除物化视图,表保持存在.