?

使用物化视图
?????? 授权物化视图权限.
?????? grant create any masterialized view to scott;
?????? 创建物化视图
?????? create materialized view mv
????? as select * from scott.emp;
?????
????? 物化视图的中的rowid和对应的表中的rowid不一致.
????? 视图中的rowid和对应表中的rowid一致.
?????
?????物化视图刷新类型:
?????? 全部(C)
???????exec dbms_mview.refresh('MV','C');
?????? 增量(F)
????????? 其实针对mv的信息至少一个update操作.
?????? 强制(?)
?????? Never:
?????
??????
??????
?????物化视图更新模式
????? 手动
???????? dbms_mview.refresh('MV',parallelism=>10);
??????多个
??????dbms_mview.refresh('MV1,MV2',parallelism=>10);
??????或者
??????dbms_mview.refresh_dependent('EMP');
??????备注:emp为mv的基表.
??????刷新所有的视图:
?????? dbms_mview.refresh_all_mviews;
??????
????? 自动(同步或者异步)
????????? 通过oracle job实现自动刷新.
????? 物化视图
???????? 在数据仓库系统中的使用.不同数据库中表的同步.
??????高级数据复制中使用.
?????查看PL中PL/SQL中package type和views;
?????
????实例如下:
?????? 使用实例证明实体化视图和视图的区别
?????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@TICKET

?????scott@TICKET> create view v as select * from t;

?????视图已创建。

?????scott@TICKET> select * from v;

???????? KEY VAL
?????---------- -------------------------
??????? 1 a
??????? 2 b
??????? 3 c

?????scott@TICKET> select rowid,a.* from t a;

?????ROWID???????????????????? KEY VAL
?????------------------ ---------- -------------------------
?????AAASzoAAEAAABHlAAA????????? 1 a
?????AAASzoAAEAAABHlAAB????????? 2 b
?????AAASzoAAEAAABHlAAC????????? 3 c

?????scott@TICKET> select rowid,a.* from v a;

?????ROWID???????????????????? KEY VAL
?????------------------ ---------- -------------------------
?????AAASzoAAEAAABHlAAA????????? 1 a
?????AAASzoAAEAAABHlAAB????????? 2 b
?????AAASzoAAEAAABHlAAC????????? 3 c

?????scott@TICKET> create materialized view mv? as
?????? 2? select * from t;

?????实体化视图已创建。

?????scott@TICKET> select rowid,a.* from mv a;

?????ROWID???????????????????? KEY VAL
?????------------------ ---------- -------------------------
?????AAASzrAAEAAABH0AAA????????? 1 a
?????AAASzrAAEAAABH0AAB????????? 2 b
?????AAASzrAAEAAABH0AAC????????? 3 c

?????由上面:
????? 查询t,v,mv的信息可以看出mv的rowid和其他的不一样.
?????
?????scott@TICKET> update t set val='aa' where k