浅析物化视图与查询重写(Enable query rewrite)

查询重写是指当对物化视图的基表进行查询时，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

 

物理读和一致性读分别为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;

 

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

 

通过物化视图的查询，发现只有4个一致性读和2个物理读

至此，说明物化视图会为系统带来一定的性能提升

 

3 将物化视图设置为enable query rewrite(默认为disable),并执行原查询语句，查看执行计划

 

SQL> alter MATERIALIZED VIEW EMP_DEPT_MV_01 enable query rewrite;

Materialized view altered.

 

 

SQL> show parameter rewrite

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

 

结果验证，该语句实际上自动查询的是与其对应的物化视图

 

总结：

 

物化视图提高查询速度，但需要额外的存储空间

查询重写: 无需改变代码，只需创建一个查询重写的物化视图即可提高系统性能

 

转自：http://blog.itpub.net/28719055/viewspace-1258720/