物化视图查询重写

已有 874 次阅读2012-12-27 16:40 |个人分类:物化视图| 查询

所谓物化视图查询重写就是，如果初始化参数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优化模式下带给我们很多新奇的体验。