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