ORACLE物化视图-Query Rewrite的一般理解之五

 对于一个给定的SQL, 和现有的MVIEW, 这个SQL可能被Rewrite, 也可能不能被Rewrite, 如何找出这其中的原因呢, 靠经验可以解决一些问题, 不过所花的时间就要长一点了. 其实在Oracle中提供了一个函数可以用于解释为什么某个SQL不能被重写, 这个过程位于dbms_mview这个包中, 过程名为explain_rewrite, 有了这个工具, 可以快速地找出为什么不能被重写, 要使用这个过程, 你需要事先创建一个表REWRITE_TABLE, 可以通过@?/rdbms/admin/utlxrw.sql来创建.

CREATE TABLE REWRITE_TABLE
(
   statement_id          VARCHAR2(30),  -- id for the query
   mv_owner              VARCHAR2(30),  -- owner of the MV
   mv_name               VARCHAR2(30),  -- name of the MV
   sequence              INTEGER,       -- sequence no of the error msg
   query                 VARCHAR2(2000),-- user query
   message               VARCHAR2(512), -- EXPLAIN_REWRITE error msg
   pass                  VARCHAR2(3),   -- rewrite pass no
   flags                 INTEGER,       -- associated flags
   reserved1             INTEGER,       -- currently not used
   reserved2             VARCHAR2(10)  -- currently not used
)

   有了这个表后, 就可以调用dbms_mview.explain_rewrite来解决问题了:

PROCEDURE EXPLAIN_REWRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY                          VARCHAR2                IN
 MV                             VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT

   下面我们来看一个例子, 先创建所需的表:

create table test_rewrite as select * from dba_objects;
alter table test_rewrite modify owner not null;
create materialized view mv_test_rewrite
enable query rewrite
as
select owner,count(*) cntrow, count(object_id) cntowner,
   sum(object_id) sumobjid, sum(data_object_id) sumobjd
FROM TEST_REWRITE
group by owner;
analyze table test_rewrite compute statistics;
analyze table mv_test_rewrite compute statistics;

   然后我们来试验几种不同情况下dbms_mvew.explain_rewrite的输出情况:

1, 未设置query_rewrite_enable为true时
SQL> alter session set query_rewrite_enabled=false;

Session altered.

SQL>  exec dbms_mview.explain_rewrite('select avg(object_id) from test_rewrite where owner=:1 group by owner');

PL/SQL procedure successfully completed.

SQL> select MESSAGE from rewrite_table;

MESSAGE
------------------------------------------------------------
QSM-01001: query rewrite not enabled

2, 试测一个不能query rewrite的SQL语句
SQL> exec dbms_mview.explain_rewrite('select avg(data_object_id) from test_rewrite where owner=:1 group by owner');

PL/SQL procedure successfully completed.

SQL> select MESSAGE from rewrite_table;

MESSAGE
------------------------------------------------------------
QSM-01067: materialized view, MV_TEST_REWRITE, cannot support the query measure, AVG

3, 再来测试一个可以rewrite的SQL语句
SQL> exec dbms_mview.explain_rewrite('select avg(object_id) from test_rewrite where owner=:1 group by owner');

PL/SQL procedure successfully completed.

SQL> select MESSAGE from rewrite_table;

MESSAGE
------------------------------------------------------------
QSM-01033: query rewritten with materialized view, MV_TEST_REWRITE

    从上面的例子中, 我们可以从查询出来的消息中发现很有用的信息, 在做上述实验之前, 我都运行了delete rewrite_table语句.