淺析物化檢視與查詢重寫(Enable query rewrite)

通過如下實驗說明，物化檢視給我們帶來的效能提高，以及查詢重寫的好處

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

結果驗證，該語句實際上自動查詢的是與其對應的物化檢視

總結：

物化檢視提高查詢速度，但需要額外的儲存空間

查詢重寫: 無需改變程式碼，只需建立一個查詢重寫的物化檢視即可提高系統效能