[20121101]物化视图与表(Materialized Views and Tables).txt

[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),建立物化视图前,先要建立表,才能物化视图时. 如果删除物化视图,表保持存在.