查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据
通过如下实验说明,物化视图给我们带来的性能提高,以及查询重写的好处
1 执行一段普通的连接查询,并查看执行计划以及统计信息
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly
SQL> select ename,job,dname
2 from emp,dept
3 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 420 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
9 physical reads
0 redo size
970 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
System altered.
SQL> set autotrace traceonly
SQL> select ename,job,dname
2 from emp,dept
3 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 420 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
9 physical reads
0 redo size
970 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
物理读和一致性读分别为11个 和9个
2 创建以上语句的物化视图,并查看通过物化视图查询所得到的执行计划以及统计信息
CREATE MATERIALIZED VIEW LOG ON emp
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON dept
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW emp_dept_mv_01
BUILD IMMEDIATE
REFRESH force ON COMMIT
as select ename,job,dname
from emp,dept
where emp.deptno=dept.deptno;
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON dept
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW emp_dept_mv_01
BUILD IMMEDIATE
REFRESH force ON COMMIT
as select ename,job,dname
from emp,dept
where emp.deptno=dept.deptno;
SQL> select * from emp_dept_mv_01;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2905768236
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS FULL| EMP_DEPT_MV_01 | 14 | 308 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
1079 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2905768236
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS FULL| EMP_DEPT_MV_01 | 14 | 308 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
1079 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
通过物化视图的查询,发现只有4个一致性读和2个物理读
至此,说明物化视图会为系统带来一定的性能提升
3 将物化视图设置为enable query rewrite(默认为disable),并执行原查询语句,查看执行计划
SQL> alter MATERIALIZED VIEW EMP_DEPT_MV_01 enable query rewrite;
Materialized view altered.
Materialized view altered.
SQL> show parameter rewrite
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
SQL> alter system flush buffer_cache;
System altered.
SQL> select ename,job,dname
2 from emp,dept
3 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1320338361
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV_01 | 14 | 308 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
1079 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
结果验证,该语句实际上自动查询的是与其对应的物化视图
总结:
物化视图提高查询速度,但
需要额外的存储空间
查询重写: 无需改变代码,只需创建一个查询重写的物化视图即可提高系统性能
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28719055/viewspace-1258720/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28719055/viewspace-1258720/