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

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