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