[20121101]物化檢視與表(Materialized Views and Tables).txt

[20121101]物化檢視與表(Materialized Views and Tables).txt

1.建立測試環境:

SQL> select * from v$version  where rownum<=1;

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),建立物化檢視前,先要建立表,才能物化檢視時. 如果刪除物化檢視,表保持存在.