ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MV】物化视图查询重写

【MV】物化视图查询重写

原创 Linux操作系统 作者:secooler 时间:2011-06-12 22:17:08 0 删除 编辑
  所谓物化视图查询重写就是,如果初始化参数query_rewrite_enabled设置为TRUE,并且数据库运行在CBO优化模式下,当对基表进行查询时,Oracle会自动判断是否能利用这个基表的所有包含ENABLE QUERY REWRITE关键字的物化视图,如果可以且根据统计信息判断通过查询物化视图代价更小,则Oracle自动重写查询语句,通过查询物化视图得到正确的结果。

1.初始化环境
1)准备物化视图基表
create table t (x int, y int,z int);
insert into t values (1,1,1);
insert into t values (2,2,2);
insert into t values (3,3,3);
insert into t values (4,4,4);
insert into t values (5,5,5);
insert into t values (6,6,6);
insert into t values (6,6,6);
commit;
sec@ora10g> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6

7 rows selected.

2)创建物化视图日志
注:包含所有字段
sec@ora10g> create materialized view log on t with sequence, rowid (x,y,z) including new values;

Materialized view log created.

3)创建物化视图
sec@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;

Materialized view created.

sec@ora10g> select * from mv_t;

         X          Y          Z   COUNT(*)
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          1
         3          3          3          1
         4          4          4          1
         5          5          5          1
         6          6          6          2

6 rows selected.

2.物化视图查询重写测试
1)启用查询重写功能(默认)
sec@ora10g> show parameter query_rewrite_enabled

NAME                  TYPE   VALUE
--------------------- ------ -----------------
query_rewrite_enabled string TRUE
sec@ora10g> alter system set query_rewrite_enabled=TRUE;

System altered.

sec@ora10g> set autotrace on
sec@ora10g> select x,y,z,count(*) from t group by x,y,z;

         X          Y          Z   COUNT(*)
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          1
         3          3          3          1
         4          4          4          1
         5          5          5          1
         6          6          6          2

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

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


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        677  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

从执行计划上可以看很清晰的看到这个查询重新的过程,我们对T表进行查询,Oracle此时发现可以通过物化视图MV_T直接返回我们需要的结果,因此最终智慧的决定通过检索物化视图来返回最后结果。

2)停用查询重写功能
sec@ora10g> alter system set query_rewrite_enabled=FALSE;

System altered.

sec@ora10g> show parameter query_rewrite_enabled

NAME                  TYPE   VALUE
--------------------- ------ -----------------
query_rewrite_enabled string FALSE

sec@ora10g> set autotrace on
sec@ora10g> select x,y,z,count(*) from t group by x,y,z;

         X          Y          Z   COUNT(*)
---------- ---------- ---------- ----------
         4          4          4          1
         3          3          3          1
         6          6          6          2
         1          1          1          1
         5          5          5          1
         2          2          2          1

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     7 |   273 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     7 |   273 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     7 |   273 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

当停用查询重写功能后,查询结果将只能通过T表进行返回。

3.小结
  当满足物化视图查询重写的条条框框后,我们便实现了高效、灵活地检索数据的目的。Oracle在CBO优化模式下带给我们很多新奇的体验。

Good luck.

secooler
11.06.12

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-697891/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8093995