查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图,转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。
/*+ REWRITE(MVIEW_NAME) */ 使用物化视图进行查询重写。
SQL> select e.empno, e.ename, e.job
2 from emp e, dept d
3 where e.deptno = d.deptno
4 and d.dname = 'RESEARCH';
EMPNO ENAME JOB
---------- ---------- ---------
7566 JONES MANAGER
7902 FORD ANALYST
7876 ADAMS CLERK
7369 SMITH CLERK
7788 SCOTT ANALYST
执行计划
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 170 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 294 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."DNAME"='RESEARCH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
SQL> set autotrace off;
SQL> create materialized view sales_time_product_mv enable query rewrite as
select e.empno, e.ename, e.job
from emp e, dept d
where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
实体化视图已创建。
SQL> set autotrace on explain;
SQL> select e.empno, e.ename, e.job
2 from emp e, dept d
3 where e.deptno = d.deptno
4 and d.dname = 'RESEARCH';
EMPNO ENAME JOB
---------- ---------- ---------
7566 JONES MANAGER
7902 FORD ANALYST
7876 ADAMS CLERK
7369 SMITH CLERK
7788 SCOTT ANALYST
执行计划
----------------------------------------------------------
Plan hash value: 663088863
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 130 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_TIME_PRODUCT_MV | 5 | 130 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
我在物化视图中使用了一个rewrite提示来打开查询重写转换。