使用物化视图查询重写提升性能

SQL> select * from v$version where rownum =1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

启用物化视图查询重写
1.设置优化器模式optimizer_mode=all_rows,first_rows,或first_rows_n。
2.设置初始化参数query_rewrite_enabled=true(默认值)或force。
3.物化视图必须使用了enable query rewrite子句。

SQL> select name,value from v$parameter where name in 
('optimizer_mode','query_rewrite_enabled','query_rewrite_integrity');
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- -----------
optimizer_mode                                                                   ALL_ROWS
query_rewrite_enabled                                                            TRUE
query_rewrite_integrity                                                          enforced

query_rewrite_integrity参数是可选的，如果指定该参数，必须将其设置为STALE_TOLERATED, TRUSTED,或 ENFORCED(默认值) 之一。
完整级别默认被设置为ENFORCED。在这种模式下，所有的约束必须被验证。如果使用了 ENABLE NOVALIDATE RELY ，有些类型的查询重写可能不再有效。这时（约束不被验证的情况）为了使用查询重写，应该将完整级别设置为较低级别（TRUSTED 或 STALE_TOLERATED）。具体的完整级别解释可查看【Basic Query Rewrite -> Accuracy of Query Rewrite】部分。

查询重写是如何工作的

This figure illustrates how query rewrites works:
First, you enter a query.
Then, Oracle generates the plan for your query.
Then, Oracle rewrites the statement.
Then, Oracle compares the cost of the two statements.
Oracle chooses the best result.

简单测试：

SQL> create table a as select owner,object_id,object_name from dba_objects;

表已创建。

SQL> create table b as select owner,object_id,object_name from dba_objects;

表已创建。

SQL> insert into a  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into a  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into a  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into a  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into a  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into b  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into b  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into b  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into b  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> insert into b  select owner,object_id,object_name from dba_objects;

已创建49868行。

SQL> commit;

提交完成。

SQL> set timing on
SQL> CREATE MATERIALIZED VIEW join_a_b_mv
  2  ENABLE QUERY REWRITE AS
  3  select a.owner ,count(*) from a,b
  4  where a.object_id=b.object_id group by a.owner;

实体化视图已创建。

已用时间:  00: 00: 06.73
SQL> set autot traceonly
SQL> select  /*+ NOREWRITE */ a.owner ,count(*) from a,b
  2  where a.object_id=b.object_id group by a.owner;

已选择18行。

已用时间:  00: 00: 01.08

执行计划
----------------------------------------------------------
Plan hash value: 583771949

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1498K|    61M|       |  1824  (10)| 00:00:22 |
|   1 |  HASH GROUP BY      |      |  1498K|    61M|       |  1824  (10)| 00:00:22 |
|*  2 |   HASH JOIN         |      |  1498K|    61M|  5760K|  1687   (3)| 00:00:21 |
|   3 |    TABLE ACCESS FULL| B    |   235K|  2991K|       |   380   (2)| 00:00:05 |
|   4 |    TABLE ACCESS FULL| A    |   314K|  9202K|       |   381   (2)| 00:00:05 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3372  consistent gets
          0  physical reads
          0  redo size
        780  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL> select a.owner ,count(*) from a,b
  2  where a.object_id=b.object_id group by a.owner;

已选择18行。

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 3977076886

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    18 |   540 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| JOIN_A_B_MV |    18 |   540 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        780  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

可见，差距还是相当大的。

参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm