Qurey rewrite is rewrite query.

To speed up the query the Oracle will choose the materialized view automatically if the parameter take effect.

SQL> create MATERIALIZED VIEW SCOTT.mv1

  2   tablespace users

  3   BUILD IMMEDIATE

  4   REFRESH COMPLETE

  5   ENABLE QUERY REWRITE

  6   AS select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

 

实体化视图已创建。

 

SQL> set autot traceonly explain

SQL> select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

SQL> set lines 345

SQL> /

 

执行计划

----------------------------------------------------------

Plan hash value: 2958490228

 

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |    14 |   210 |     3   (0)| 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |    14 |   210 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Note

-----

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

 

Close the query rewrite option.

SQL> ALTER SESSION SET query_rewrite_enabled =false;

 

会话已更改。

 

SQL> select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

 

执行计划

----------------------------------------------------------

Plan hash value: 844388907

 

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |   280 |     6   (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |         |    14 |   280 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |        |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

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

       filter("E"."DEPTNO"="D"."DEPTNO")