通过阅读原文：http://www.anysql.net/oracle/query_rewrite_01.html
帮助很大，自己也动手练习了一下，记录一下学习成果喽~

Tom在<>一书中将实体化视图(MView)称为是数据仓库的索引, 这是再贴切不过的了, 在OLTP中当SELECT语句的所有的字段都在索引中时, Oracle可以不从表读数据, 而直接从索引中获得全部信息, 而Query Rewrite则是通过创建中间表, 让Oracle自动从创建的中间表读取数据, 而不需要从原表读取了, 这个中间表可以是预先join好的或预先计算好的中间结果. 他的使用就和一般的索引同理了, 虽然你指定的还是那个大表, 但oracle可以为你自动识别可以从那个"数据仓库索引"中读取数据. 示例如下：

SQL> CONN SCOTT/TIGER
SQL> CREATE TABLE D_TABLE
2 AS SELECT OWNER,OBJECT_NAME FROM sys.dba_objects;

表已创建。

SQL> CREATE MATERIALIZED VIEW M_TABLE ENABLE QUERY REWRITE
2 AS
3 SELECT OWNER,COUNT(*) TABCNT FROM DETAIL_TABLE GROUP BY OWNER;

实体化视图已创建。

现在假设D_TABLE是一个非常大的表, M_TABLE是预先计算好的中间表, 我们来做一个查询测试一下, 要让Oracle使用Query Rewrite,首先要使用CBO,对这个两个表进行分析, 然后将query_rewrite_enabled参数设为True（这个参数可以在session级别更改）. 如:

SQL> alter session set query_rewrite_enabled = true;

会话已更改。

SQL> show parameter query_rewrite_enabled

NAME TYPE VALUE
———————————— ———– ———
query_rewrite_enabled string true

SQL> select owner,count(*) from d_table where wner = 'SYSTEM' group by owner;

OWNER COUNT(*)
------------------------------ ----------
SYSTEM 570

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

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

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

1 - filter("M_TABLE"."OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

======================================================================
SQL> alter session set query_rewrite_enabled = false;

会话已更改。

SQL> show parameter query_rewrite_enabled

NAME TYPE VALUE
———————————— ———– ———
query_rewrite_enabled string FALSE

SQL> select owner,count(*) from d_table where wner = 'SYSTEM' group by owner;

OWNER COUNT(*)
------------------------------ ----------
SYSTEM 570

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

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 255 | 4335 | 106 (1)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 255 | 4335 | 106 (1)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | D_TABLE | 255 | 4335 | 106 (1)| 00:00:02 |
--------------------------------------------------------------------------------

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

2 - filter("OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement (level=2)

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

可看到上面两个同样的语句在query rewrite时启用和禁用时的plan的区别, M_TABLE的作用就象是一个索引. 除了Query_rewrite_enabled参数外, 还有一个参数query_rewrite_integrity会影响Query Rewrite的使用.

2.
在Oracle的Query Rewrite中, 一些约束的声明或状态和Oracle决于可否Query Rewrite有很大的关系. ENFORCED表示Oracle只相信Enabled和Validated的约束, 而Trusted则相信RELY的约束, 就算这个约束没有Enabled和Validated, 这两种都要求MVIEW中的数据是及时刷新的,而STALE_TOLERATED则可以容忍一切, 就算中间表的数据是旧的, 指基表有新数据修改而MVIEW还没有刷新的情况下, Oracle也会选择使用Query Rewrite来作查询, 在这种情况下, 查出来的数据可能是不准的. 下面我们来作一个例子来显示enforced与trusted的不同，并且直接使用scott.emp和scott.dept主外键关系，此实验一共三张表和一个物化视图表:

SQL> show parameter query

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> create materialized view mv_table enable query rewrite as
2 select e.deptno, count(*) from emp e,dept d where e.deptno=d.deptno group by e.deptno;

实体化视图已创建。

SQL> create table u_table as select deptno from emp;

表已创建。

SQL> select u.deptno ,count(*) from u_table u ,dept d where u.deptno = d.deptno group by u.deptno;

DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3

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

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 224 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 224 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 14 | 224 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| U_TABLE | 14 | 182 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

4 - access("U"."DEPTNO"="D"."DEPTNO")

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
14 recursive calls
0 db block gets
24 consistent gets
1 physical reads
0 redo size
538 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

============================================================

SQL> alter session set query_rewrite_integrity = trusted;

会话已更改。

SQL> select u.deptno ,count(*) from u_table u ,dept d where u.deptno = d.deptno group by u.deptno;

DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3

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

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 224 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 224 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 14 | 224 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| U_TABLE | 14 | 182 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

4 - access("U"."DEPTNO"="D"."DEPTNO")

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
245 recursive calls
4 db block gets
445 consistent gets
6 physical reads
0 redo size
538 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
59 sorts (memory)
0 sorts (disk)
3 rows processed

通过对比统计信息可得出结论。

ITPub博客

【ORACLE】物化视图与QUERY REWRITE(一）

最新文章