Oracle 性能调优学习笔记(十六)--- 使用物化视图
?
使用物化视图
?????? 授权物化视图权限.
?????? 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 key=1;
?????已更新 1 行。
?????scott@TICKET> commit;
?????提交完成。
?????scott@TICKET> select * from t;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 b
??????? 3 c
?????scott@TICKET> select * from v;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 b
??????? 3 c
?????scott@TICKET> select * from mv;
???????? KEY VAL
?????---------- -------------------------
??????? 1 a
??????? 2 b
??????? 3 c
?????由上面可以查看当基表t变化,t和v的查询结果相应的发生变化.但是mv的数据不变化.
?????scott@TICKET> host
?????scott@TICKET> conn /as sysdba
?????已连接。
?????GLOBAL_NAME
?????--------------------------------------------
?????sys@TICKET
?????sys@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 c
?????scott@TICKET> update t set val='aa' where key=2;
?????已更新 1 行。
?????scott@TICKET> select * from t;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 aa
??????? 3 c
?????scott@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 abc
?????scott@TICKET> select * from v;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 aa
??????? 3 abc
?????scott@TICKET> select * from mv;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 b
??????? 3 c
?????scott@TICKET> select * from mv_t;
???????? KEY VAL
?????---------- -------------------------
??????? 1 aa
??????? 2 aa
??????? 3 c
?????scott@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????????????????????????? TABLE
?????SALGRADE?????????????????????? TABLE
?????EMP_ENAME_IDX????????????????? INDEX
?????EMP_JOB_IDX??????????????????? INDEX
?????SUPPLIER?????????????????????? TABLE
?????EMP_TEMP?????????????????????? TABLE
?????MLOG$_EMP????????????????????? TABLE
?????RUPD$_EMP????????????????????? TABLE
?????TEST?????????????????????????? TABLE
?????SALES_DELTA??????????????????? TABLE
?????DEPT?????????????????????????? TABLE
?????EMP??????????????????????????? TABLE
?????PK_EMP???????????????????????? INDEX
?????PK_DEPT??????????????????????? INDEX
?????TBS??????????????????????????? TABLE
?????SYS_C0015204?????????????????? INDEX
?????T????????????????????????????? TABLE
?????V????????????????????????????? VIEW
?????MV???????????????????????????? TABLE
?????SYS_C0015205?????????????????? INDEX
?????MV???????????????????????????? MATERIALIZED VIEW
?????MLOG$_T??????????????????????? TABLE
?????RUPD$_T??????????????????????? TABLE
?????MV_T?????????????????????????? TABLE
?????SYS_C0015206?????????????????? INDEX
?????MV_T?????????????????????????? MATERIALIZED VIEW
?????MV_T2????????????????????????? TABLE
?????SYS_C0015207?????????????????? INDEX
?????MV_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)
?????----------
??????? 2
?????scott@TICKET> commit;
?????提交完成。
?????scott@TICKET> select count(1) from mlog$_t;
?????? COUNT(1)
?????----------
??????? 2
?????scott@TICKET> select rowid,a.* from mv a;
?????ROWID???????????????????? KEY VAL
?????------------------ ---------- -------------------------
?????AAASzrAAEAAABH0AAA????????? 1 aa
?????AAASzrAAEAAABH0AAB????????? 2 aa
?????AAASzrAAEAAABH0AAC????????? 3 abc
?????scott@TICKET> spool off;
?????参考官方网站:http://download.oracle.com/docs/cd/B10501_01/server.920/a96568/rarmviea.htm
?
?
