源码-Oracle数据库管理-第七章-视图-Part 3（物化视图）

于 2016-10-02 22:56:43 发布
阅读量1.3k 收藏
点赞数
分类专栏： 数据库（DB） 文章标签： 物化视图materialized vie 查询重写query rewrite 实体化视图
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52725183
版权

物化视图：又称为实体化视图，它通过将查询语句的结果存储到物理的内部表中，在查询数据库数据时直接从物化视图保存的数据中提取数据，它常用于数据仓库环境、复杂的汇总和聚合、物化视图复制和移动的计算环境中。

查询重写：如果用户创建了一个物化视图，当用户查询主表中的数据时，Oracle的查询重写技术（Query Rewrite）会自动将对主表的查询转换为对物化视图的查询。


PS：今天尝试用spool d:/test.txt 及spool out命令将sqlplus命令行中的内容输出到文本，然后粘贴于此。这也是教科书上的做法，有利于更全面的掌握SQL(PL/SQL)的执行过程。


scott_pd@ORCL> conn sys as sysdba
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCL

sys_pd@ORCL> grant create materialized view to scott;

授权成功。

sys_pd@ORCL> grant query rewrite to scott;

授权成功。

sys_pd@ORCL> grant create any table to scott;

授权成功。

sys_pd@ORCL> grant select any table to scott;

授权成功。

sys_pd@ORCL> CREATE   MATERIALIZED VIEW my_table_aggs'/
  2  /
CREATE   MATERIALIZED VIEW my_table_aggs'/
                                        *
第 1 行出现错误:
ORA-01756: 引号内的字符串没有正确结束


sys_pd@ORCL> CREATE   MATERIALIZED VIEW my_table_aggs
  2         BUILD IMMEDIATE
  3         REFRESH ON COMMIT
  4         ENABLE QUERY REWRITE
  5         AS
  6         SELECT owner, COUNT(*)
  7          FROM ma_table
  8         GROUP BY owner;
        FROM ma_table
             *
第 7 行出现错误:
ORA-00942: 表或视图不存在


sys_pd@ORCL> conn scott/tiger;
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCL

scott_pd@ORCL> CREATE   MATERIALIZED VIEW my_table_aggs
  2         BUILD IMMEDIATE
  3         REFRESH ON COMMIT
  4         ENABLE QUERY REWRITE
  5         AS
  6         SELECT owner, COUNT(*)
  7          FROM ma_table
  8         GROUP BY owner;

实体化视图已创建。

scott_pd@ORCL> set autotrace traceonly explain statistics;
scott_pd@ORCL> select owner, count(*) from ma_table group by owern;
select owner, count(*) from ma_table group by owern
                                              *
第 1 行出现错误:
ORA-00904: "OWERN": 标识符无效


scott_pd@ORCL> select owner, count(*) from ma_table group by owner;

已选择16行。


执行计划
----------------------------------------------------------
Plan hash value: 3709818580

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |    16 |   480 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MY_TABLE_AGGS |    16 |   480 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

scott_pd@ORCL> desc mlog$_emp;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                          NUMBER(4)
 SNAPTIME$$                                                     DATE
 DMLTYPE$$                                                      VARCHAR2(1)
 OLD_NEW$$                                                      VARCHAR2(1)
 CHANGE_VECTOR$$                                                RAW(255)
 XID$$                                                          NUMBER

scott_pd@ORCL> select * from mlog$_emp;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |   168 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     1 |   168 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
        124  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott_pd@ORCL> select * from mlog$_emp;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |   168 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     1 |   168 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
        108  redo size
        699  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott_pd@ORCL> INSERT INTO emp VALUES(8099,'张大百','分析员',7369,SYSDATE,2000,100,20);
INSERT INTO emp VALUES(8099,'张大百','分析员',7369,SYSDATE,2000,100,20)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMP)


scott_pd@ORCL> 
scott_pd@ORCL> INSERT INTO emp VALUES(8089,'张大百','分析员',7369,SYSDATE,2000,100,20);

已创建 1 行。


执行计划
----------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |    41 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP  |       |       |            |          |
---------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         28  recursive calls
         19  db block gets
         18  consistent gets
          0  physical reads
       2404  redo size
       1136  bytes sent via SQL*Net to client
       1328  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott_pd@ORCL> select * from mlog$_emp;


执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |   336 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     2 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1002  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

scott_pd@ORCL> set traceonly off;
SP2-0158: 未知的 SET 选项 "traceonly"
scott_pd@ORCL> set trace off;
SP2-0158: 未知的 SET 选项 "trace"
scott_pd@ORCL> set autotrace explain statistics;
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
scott_pd@ORCL> set autotrace on explain statistics;
scott_pd@ORCL> select * from mlog$_emp;

     EMPNO SNAPTIME$$     DM OL
---------- -------------- -- --
CHANGE_VECTOR$$
----------------------------------------------------------------------------------------------------
     XID$$
----------
      8089 01-1月 -00     I  N
FEFF
5.6308E+14

      8099 01-1月 -00     I  N
FEFF
2.2518E+15



执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |   336 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     2 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1002  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

scott_pd@ORCL> col change_vectors$$ for a20;
scott_pd@ORCL> select * from mlog$_emp;

     EMPNO SNAPTIME$$     DM OL
---------- -------------- -- --
CHANGE_VECTOR$$
----------------------------------------------------------------------------------------------------
     XID$$
----------
      8089 01-1月 -00     I  N
FEFF
5.6308E+14

      8099 01-1月 -00     I  N
FEFF
2.2518E+15



执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |   336 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     2 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1002  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

scott_pd@ORCL> col change_vector$$ for a20;
scott_pd@ORCL> select * from mlog$_emp;

     EMPNO SNAPTIME$$     DM OL CHANGE_VECTOR$$           XID$$
---------- -------------- -- -- -------------------- ----------
      8089 01-1月 -00     I  N  FEFF                 5.6308E+14
      8099 01-1月 -00     I  N  FEFF                 2.2518E+15


执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |   336 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     2 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1002  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

scott_pd@ORCL> INSERT INTO emp VALUES(8091,'黄阔','顾问',7369,SYSDATE,2000,100,20);

已创建 1 行。


执行计划
----------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |    41 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP  |       |       |            |          |
---------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         22  recursive calls
         16  db block gets
         17  consistent gets
          0  physical reads
       2280  redo size
       1138  bytes sent via SQL*Net to client
       1322  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott_pd@ORCL> select * from mlog$_emp;

     EMPNO SNAPTIME$$     DM OL CHANGE_VECTOR$$           XID$$
---------- -------------- -- -- -------------------- ----------
      8089 01-1月 -00     I  N  FEFF                 5.6308E+14
      8091 01-1月 -00     I  N  FEFF                 5.6308E+14
      8099 01-1月 -00     I  N  FEFF                 2.2518E+15


执行计划
----------------------------------------------------------
Plan hash value: 991062009

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     3 |   504 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MLOG$_EMP |     3 |   504 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1035  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

scott_pd@ORCL> select * from emp_data;

    EMP_ID      EMPNO ENAME
---------- ---------- ----------------------------------------
         1       7369 李强


执行计划
----------------------------------------------------------
Plan hash value: 4079136661

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_DATA |     1 |    14 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         64  recursive calls
          0  db block gets
         16  consistent gets
          5  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott_pd@ORCL> set autotrace off;
scott_pd@ORCL> select * from emp_data;

    EMP_ID      EMPNO ENAME
---------- ---------- ----------------------------------------
         1       7369 李强

scott_pd@ORCL> exec dbms_mview.refresh('EMP_DATA','F');
BEGIN dbms_mview.refresh('EMP_DATA','F'); END;

*
第 1 行出现错误:
ORA-23401: 实体化视图 "SCOTT"."EMP_DATA" 不存在
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 1


scott_pd@ORCL> CREATE MATERIALIZED VIEW EMP_DATA REFERESH FAST AS SELECT * FROM EMP;
CREATE MATERIALIZED VIEW EMP_DATA REFERESH FAST AS SELECT * FROM EMP
                                  *
第 1 行出现错误:
ORA-00905: 缺失关键字


scott_pd@ORCL> CREATE MATERIALIZED VIEW EMP_DATA REFRESH FAST AS SELECT * FROM EMP;
CREATE MATERIALIZED VIEW EMP_DATA REFRESH FAST AS SELECT * FROM EMP
                                                                *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用


scott_pd@ORCL> DROP materialized view emp_data;
DROP materialized view emp_data
*
第 1 行出现错误:
ORA-12003: 实体化视图 "SCOTT"."EMP_DATA" 不存在


scott_pd@ORCL> CREATE MATERIALIZED VIEW EMP_DATA REFRESH FAST AS SELECT * FROM EMP;
CREATE MATERIALIZED VIEW EMP_DATA REFRESH FAST AS SELECT * FROM EMP
                                                                *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用

scott_pd@ORCL> CREATE MATERIALIZED VIEW EMP_DATA REFRESH FAST AS SELECT * FROM EMP;

实体化视图已创建。

scott_pd@ORCL> exec dbms_mview.refresh('EMP_DATA','F');

PL/SQL 过程已成功完成。

scott_pd@ORCL> select count(*) from emp_data;

  COUNT(*)
----------
        22

scott_pd@ORCL> INSERT INTO emp VALUES(8091,'黄阔','顾问',7369,SYSDATE,2000,100,20);
INSERT INTO emp VALUES(8091,'黄阔','顾问',7369,SYSDATE,2000,100,20)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMP)


scott_pd@ORCL> INSERT INTO emp VALUES(8092,'黄阔','顾问',7369,SYSDATE,2000,100,20);

已创建 1 行。

scott_pd@ORCL> select count(*) from emp_data;

  COUNT(*)
----------
        22

scott_pd@ORCL> exec dbms_mview.refresh('EMP_DATA','F');

PL/SQL 过程已成功完成。

scott_pd@ORCL> select count(*) from emp_data;

  COUNT(*)
----------
        23

scott_pd@ORCL> spool out

create table ma_table nologging as
select * from all_objects union all
select * from all_objects;

create table test as select * from emp;
drop table test;
select * from test;



--创建一个物化视图
CREATE   MATERIALIZED VIEW my_table_aggs
       BUILD IMMEDIATE
       REFRESH ON COMMIT
       ENABLE QUERY REWRITE
       AS
       SELECT owner, COUNT(*)
        FROM ma_table
       GROUP BY owner


--使用最简单的语法创建物化视图
CREATE MATERIALIZED VIEW ma_emp_count AS
    SELECT deptno,COUNT(*) deptnum FROM emp GROUP BY deptno;
    
    
--创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON emp  --创建物化视图日志
                   WITH PRIMARY KEY                        --指定在物化视图日志中记录更改主键
                   INCLUDING NEW VALUES;



--创建快速物化视图
CREATE MATERIALIZED VIEW emp_data
   REFRESH FAST
   AS SELECT * FROM emp;    
   
--向基表中插入数据
INSERT INTO emp VALUES(8099,'张大百','分析员',7369,SYSDATE,2000,100,20);


--手动刷新物化视图
exec dbms_mview.refresh('EMP_DATA','F');
select * from emp_data;
drop table emp_data;
--更改物化视图
ALTER MATERIALIZED VIEW emp_data
   REFRESH COMPLETE                      --指定完全刷新模式
   START WITH TRUNC(SYSDATE+1) + 9/24    --起始刷新日期是明天的9点
   NEXT SYSDATE+7;                       --刷新的周期是一周之后

--更改物化视图
ALTER MATERIALIZED VIEW emp_data
   REFRESH WITH PRIMARY KEY
   ENABLE QUERY REWRITE;    
   
--删除物化视图
drop materialized view emp_data;
drop materialized view log on emp;




预见未来to50
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值