Oracle使用实例证明实体化视图和视图的区别
使用实例证明实体化视图和视图的区别scott@TICKET> show user;USER 为 "SCOTT"scott@TICKET> create table t( key int primary key, val varchar(25));create table t( key int primary key, val varchar(25)) *第 1 行出现错误:ORA-00955: 名称已由现有对象使用scott@TICKET> drop table t;表已删除。创建基础表scott@TICKET> create table t( key int primary key, val varchar(25));表已创建。插入基础数据scott@TICKET> insert into t values(1,'a');已创建 1 行。scott@TICKET> insert into t values(2,'b');已创建 1 行。scott@TICKET> insert into t values(3,'c');已创建 1 行。scott@TICKET> commit;提交完成。scott@TICKET> select * from t; KEY VAL---------- ------------------------- 1 a 2 b 3 c创建视图和物化视图scott@TICKET> create view v as select * from t;create view v as select * from t *第 1 行出现错误:ORA-01031: 权限不足scott@TICKET> conn / as sysdba已连接。GLOBAL_NAME--------------------------------------------sys@TICKET给scott创建视图和物化视图的授权sys@TICKET> grant create any view ,create any materialized view to scott;授权成功。sys@TICKET> conn scott/tiger已连接。GLOBAL_NAME--------------------------------------------scott@TICKETscott@TICKET> create view v as select * from t;视图已创建。scott@TICKET> select * from v; KEY VAL---------- ------------------------- 1 a 2 b 3 cscott@TICKET> select rowid,a.* from t a;ROWID KEY VAL------------------ ---------- -------------------------AAASzoAAEAAABHlAAA 1 aAAASzoAAEAAABHlAAB 2 bAAASzoAAEAAABHlAAC 3 cscott@TICKET> select rowid,a.* from v a;ROWID KEY VAL------------------ ---------- -------------------------AAASzoAAEAAABHlAAA 1 aAAASzoAAEAAABHlAAB 2 bAAASzoAAEAAABHlAAC 3 cscott@TICKET> create materialized view mv as 2 select * from t;实体化视图已创建。scott@TICKET> select rowid,a.* from mv a;ROWID KEY VAL------------------ ---------- -------------------------AAASzrAAEAAABH0AAA 1 aAAASzrAAEAAABH0AAB 2 bAAASzrAAEAAABH0AAC 3 c由上面: 查询t,v,mv的信息可以看出mv的rowid和其他的不一样. scott@TICKET> update t set val='aa' where key=1;已更新 1 行。scott@TICKET> commit;提交完成。scott@TICKET> select * from t; KEY VAL---------- ------------------------- 1 aa 2 b 3 cscott@TICKET> select * from v; KEY VAL---------- ------------------------- 1 aa 2 b 3 cscott@TICKET> select * from mv; KEY VAL---------- ------------------------- 1 a 2 b 3 c由上面可以查看当基表t变化,t和v的查询结果相应的发生变化.但是mv的数据不变化.scott@TICKET> hostscott@TICKET> conn /as sysdba已连接。GLOBAL_NAME--------------------------------------------sys@TICKETsys@TICKET> conn scott/tiger已连接。GLOBAL_NAME--------------------------------------------scott@TICKET刷新物化视图的信息scott@TICKET> exec dbms_mview.refresh('MV');PL/SQL 过程已成功完成。scott@TICKET> select * from mv; KEY VAL---------- ------------------------- 1 aa 2 b 3 cscott@TICKET> update t set val='aa' where key=2;已更新 1 行。scott@TICKET> select * from t; KEY VAL---------- ------------------------- 1 aa 2 aa 3 cscott@TICKET> select * from v; KEY VAL---------- ------------------------- 1 aa 2 aa 3 c由上面可以看出mv刷新之后,mv,t,v的数据一致.scott@TICKET> select * from mv; KEY VAL---------- ------------------------- 1 aa 2 b 3 c在创建物化视图的时候指定为快速更新视图scott@TICKET> create materialized view mv_t refresh fast as select * from t;create materialized view mv_t refresh fast as select * from t *第 1 行出现错误:ORA-23413: 表 "SCOTT"."T" 不带实体化视图日志scott@TICKET> create materialized view log on t;实体化视图日志已创建。scott@TICKET> create materialized view mv_t refresh fast as select * from t;实体化视图已创建。scott@TICKET> create materialized view mv_t2 refresh fast as select t.* from t;实体化视图已创建。由以上可以看出创建物化视图时候指定为快速更新视图必须先创建实体化视图日志,在创建实体化视图.scott@TICKET> update t set val='abc' where key=3;已更新 1 行。scott@TICKET> commit;提交完成。scott@TICKET> select * from t; KEY VAL---------- ------------------------- 1 aa 2 aa 3 abcscott@TICKET> select * from v; KEY VAL---------- ------------------------- 1 aa 2 aa 3 abcscott@TICKET> select * from mv; KEY VAL---------- ------------------------- 1 aa 2 b 3 cscott@TICKET> select * from mv_t; KEY VAL---------- ------------------------- 1 aa 2 aa 3 cscott@TICKET> select * from mv_t2; KEY VAL---------- ------------------------- 1 aa 2 aa 3 c执行实体物化视图全部刷新scott@TICKET> exec dbms_mview.refresh('MV','C');PL/SQL 过程已成功完成。执行实体物化视图快速刷新scott@TICKET> exec dbms_mview.refresh('MV','F');PL/SQL 过程已成功完成。执行实体物化视图强制刷新scott@TICKET> exec dbms_mview.refresh('MV','?');PL/SQL 过程已成功完成。查看物化视图的表信息scott@TICKET> desc user_objects; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)scott@TICKET> col object_name for a30;scott@TICKET> col object_type for a20;scott@TICKET> select object_name,object_type from user_objects;OBJECT_NAME OBJECT_TYPE------------------------------ --------------------BONUS TABLESALGRADE TABLEEMP_ENAME_IDX INDEXEMP_JOB_IDX INDEXSUPPLIER TABLEEMP_TEMP TABLEMLOG$_EMP TABLERUPD$_EMP TABLETEST TABLESALES_DELTA TABLEDEPT TABLEEMP TABLEPK_EMP INDEXPK_DEPT INDEXTBS TABLESYS_C0015204 INDEXT TABLEV VIEWMV TABLESYS_C0015205 INDEXMV MATERIALIZED VIEWMLOG$_T TABLERUPD$_T TABLEMV_T TABLESYS_C0015206 INDEXMV_T MATERIALIZED VIEWMV_T2 TABLESYS_C0015207 INDEXMV_T2 MATERIALIZED VIEW已选择29行。由以上信息可以看出 1.在对象信息(MV_T,MV_T2,MV)可以看出物化视图均对应一个同名称的表.由此可以解释为当t表更新时候,mv不更新的原因,mv实际是把基表对应的查询结果放在一个表中,查询mv查询结果集的信息.每 2.每一个实体化视图日志对应的对应的表如t创建实体化视图日志,对应的表为mlog$_t. 命名规则为:mlog$_<TBS_NAME>scott@TICKET> desc mlog$_t; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- KEY NUMBER(38) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255)scott@TICKET> update t set val='aaab' where key=1;已更新 1 行。scott@TICKET> select count(1) from mlog$_t; COUNT(1)---------- 2scott@TICKET> commit;提交完成。scott@TICKET> select count(1) from mlog$_t; COUNT(1)---------- 2scott@TICKET> select rowid,a.* from mv a;ROWID KEY VAL------------------ ---------- -------------------------AAASzrAAEAAABH0AAA 1 aaAAASzrAAEAAABH0AAB 2 aaAAASzrAAEAAABH0AAC 3 abcscott@TICKET> spool off;参考官方网站:http://download.oracle.com/docs/cd/B10501_01/server.920/a96568/rarmviea.htm?