验证物化视图（materialized view）是否query rewrite

可参考的官方文档：

Oracle® Database PL/SQL Packages and Types Reference

Oracle® Database Data Warehousing Guide

验证是否query rewrite

Because query rewrite occurs transparently, special steps have to be taken to verify that a query has been rewritten. Of course, if the query runs faster, this should indicate that rewrite has occurred, but that is not proof.

验证是否发生可以使用的工具：

ü  EXPLAINPLAN语句

ü  DBMS_MVIEW.EXPLAIN_REWRITE过程.

使用dbms_mview.explain_mview可以知道物化视图是否可以查询重写，用法之前有过小结：http://blog.csdn.net/tanqingru/article/details/7439417

使用 EXPLAIN PLAN

是否发生查询重写看MAT_VIEWREWRITEACCESS. 如果有，则查询重写发生：

实例测试

使用explain plan查看是否查询重写的例子

SQL> create materialized view emp_loc_mv

  as

  select e.empno,e.ename,e.deptno

  from emp e,dept d

  where e.deptno=d.deptno

  and d.loc='DALLAS';

用explain plan，结果默认存放在plan_table中。Plan_table需要由utlxplan.sql创建。类似于执行计划，explain plan 不会真正执行SQL查询

SQL> explain plan for

select e.empno,e.ename,e.deptno

  from emp e,dept d

  where e.deptno=d.deptno

  and d.loc='DALLAS';

SQL> select operation,object_name from plan_table;

 

OPERATION                      OBJECT_NAME

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

SELECT STATEMENT

MERGE JOIN

TABLE ACCESS                   DEPT

INDEX                          PK_DEPT

SORT

TABLE ACCESS                   EMP

 

6 rows selected.

从以上的结果可以判断没有使用查询重写

对物化视图进行修改

SQL> alter materialized view emp_loc_mv enable query rewrite;

再次查看

SQL> explain plan for

select e.empno,e.ename,e.deptno

  from emp e,dept d

  where e.deptno=d.deptno

  and d.loc='DALLAS';

SQL> select operation,object_name from plan_table;

 

OPERATION                      OBJECT_NAME

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

SELECT STATEMENT

MERGE JOIN

TABLE ACCESS                   DEPT

INDEX                          PK_DEPT

SORT

TABLE ACCESS                   EMP

SELECT STATEMENT

MAT_VIEW REWRITE ACCESS        EMP_LOC_MV

 

8 rows selected.

 

 

使用EXPLAIN_REWRITE Procedure

1.官方文档小结

用这个过程可以知道查询生写为什么失败，如果成功，哪些物化视图被使用了。可以根据结果做调整。和explain plan一样The query specified in the EXPLAIN_REWRITE statement 没有真正执行。

A demo file, xrwutl.sql, is available to help format the output from EXPLAIN_REWRITE.

DBMS_MVIEW.EXPLAIN_REWRITE 的输出有两种方法. 一种是用table,另一种是用varray变量

使用table:

先运行utlxrw.sql创建rewrite_table

DBMS_MVIEW.EXPLAIN_REWRITE (

    query           VARCHAR2,

    mv              VARCHAR2(30),

    statement_id    VARCHAR2(30));

The query parameter is a text string representing the SQL query. The parameter, mv, is a fully qualified materialized view name in the form of schema.mv是可选参数. When it is not specified, EXPLAIN_REWRITE returns any relevant messages regarding all the materialized views considered for rewriting the given query. When schema is omitted and only mv is specified, EXPLAIN_REWRITE looks for the materialized view in the current schema.

使用 VARRAY instead of a table

DBMS_MVIEW.EXPLAIN_REWRITE (

    query           [VARCHAR2 | CLOB],

    mv               VARCHAR2(30),

    output_array     SYS.RewriteArrayType);

EXPLAIN_REWRITE Procedure Parameters

Parameter	Description
query	SQL SELECT statement to be explained
mv	The fully qualified name of an existing materialized view in the form of SCHEMA.MV. For multiple materialized views, you can provide a comma-delimited list of names.
statement_id	A client-supplied unique identifier to distinguish output messages
msg_array	The PL/SQL VARRAY that receives the output. Use this parameter to direct EXPLAIN_REWRITE's output to a PL/SQL VARRAY.

2.实例测试（table）

SQL> @?/rdbms/demo/xrwutl.sql;

SQL> @?/rdbms/admin/utlxrw.sql;

 

SQL> execute dbms_mview.explain_rewrite ( - 

'select e.empno,e.ename,e.deptno -

from emp e,dept d where e.deptno=d.deptno and d.loc=''DALLAS'' ',-

'EMP_LOC_MV');

---注意，SQL里如果有单引号时，用两个单引号（不是双引号）代替

查看结果

SQL> select message from rewrite_table order by sequence;

MESSAGE

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

QSM-01151: query was rewritten

QSM-01209: query rewritten with materialized view, EMP_LOC_MV, using text match

algorithm

2 rows selected.

结果显示启用了查询重写。

下面是一个没有使用查询重写的例子：

SQL> alter session set query_rewrite_enabled=false;

SQL> truncate table rewrite_table：

SQL> execute dbms_mview.explain_rewrite ( - 

'select e.empno,e.ename,e.deptno -

from emp e,dept d where e.deptno=d.deptno and d.loc=''DALLAS'' ',-

'EMP_LOC_MV');

SQL>  select message from rewrite_table order by sequence;

MESSAGE

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

QSM-01150: query did not rewrite

QSM-01001: query rewrite not enabled

2 rows selected.

结果显示没有使用查询重写。并列出了为什么没有启用查询重写