查询转换——使用物化视图进行查询重写（4）！

查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。

/*+ 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提示来打开查询重写转换。