所謂物化檢視查詢重寫就是，如果初始化引數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最佳化模式下帶給我們很多新奇的體驗。

Good luck.

secooler
11.06.12

-- The End --

【MV】物化檢視查詢重寫

相關文章