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】部分。
查询重写是如何工作的
![](http://hi.csdn.net/attachment/201112/24/0_1324738535TjU1.gif)
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