21:06:39 SYS@ENMOEDU > conn elaine/oracle

Connected.

21:06:56 ELAINE@ENMOEDU > create table test

21:07:21   2  as

21:07:23   3  select d.file_name,d.file_id,d.TABLESPACE_NAME

21:07:25   4  from dba_data_files d,dba_tablespaces t

21:07:32   5  where d.TABLESPACE_NAME=t.TABLESPACE_NAME;

Table created.

21:07:32 ELAINE@ENMOEDU > select * from test;

FILE_NAME                                                     FILE_ID TABLESPACE_NAME

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

/u01/app/oracle/oradata/ENMO/ENMOEDU/users01.dbf            4 USERS

/u01/app/oracle/oradata/ENMO/ENMOEDU/undotbs01.dbf          3 UNDOTBS1

/u01/app/oracle/oradata/ENMO/ENMOEDU/sysaux01.dbf           2 SYSAUX

/u01/app/oracle/oradata/ENMO/ENMOEDU/system01.dbf           1 SYSTEM

21:10:39 ELAINE@ENMOEDU > create materialized view test_mv

21:10:46   2  enable query rewrite as

21:10:51   3  select file_name,tablespace_name from test;

Materialized view created.

创建了一个物化视图test_mv，里面含有test表的file_name,tablespace_name两列。

（2）使用explain plan

21:10:56 ELAINE@ENMOEDU > explain plan for

21:11:53   2  select file_name,tablespace_name from test;

Explained.

21:12:03 ELAINE@ENMOEDU > select operation,object_name from plan_table;

OPERATION                      OBJECT_NAME

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

SELECT STATEMENT

MAT_VIEW REWRITE ACCESS        TEST_MV

经验证(1)：在执行select file_name,tablespace_name from test;语句时，Oracle使用了物化视图TEST_MV进行查询重写。

21:13:05 ELAINE@ENMOEDU > explain plan for

21:13:20   2  select file_name,file_id,tablespace_name from test;

Explained.

21:13:44 ELAINE@ENMOEDU > select operation,object_name from plan_table;

OPERATION                      OBJECT_NAME

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

SELECT STATEMENT

MAT_VIEW REWRITE ACCESS        TEST_MV

SELECT STATEMENT

TABLE ACCESS                   TEST

经验证(2)：在执行select file_name,file_id,tablespace_name from test;语句时，Oracle没有查询重写。

为什么没有进行重写？怎么样才能使用查询重写的功能？

    可以使用DBMS_MVIEW.EXPLAIN_REWRITE进行查看。

（3）创建REWRITE_TABLE

21:24:18 SYS@ENMOEDU > @?/rdbms/admin/utlxrw.sql

Table created.

（4）使用DBMS_MVIEW.EXPLAIN_REWRITE

21:25:18 ELAINE@ENMOEDU > exec DBMS_MVIEW.EXPLAIN_REWRITE ('select file_name,file_id,tablespace_name from test');

PL/SQL procedure successfully completed.

21:26:18 ELAINE@ENMOEDU > SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE

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

QSM-01150: query did not rewrite

QSM-01082: Joining materialized view, TEST_MV, with table, TEST, not possible

QSM-01102: materialized view, TEST_MV, requires join back to table, TEST, on column, FILE_ID

接下来可以顺便查看一下：执行语句select file_name,tablespace_name from test;时发生了查询重写，为什么会使用查询重写？也可以使用DBMS_MVIEW.EXPLAIN_REWRITE找到相应的信息。

21:26:36 ELAINE@ENMOEDU > exec DBMS_MVIEW.EXPLAIN_REWRITE ('select file_name,tablespace_name from test');

PL/SQL procedure successfully completed.

21:27:28 ELAINE@ENMOEDU > SELECT message FROM rewrite_table ORDER BY sequence; 

MESSAGE

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

QSM-01150: query did not rewrite

QSM-01151: query was rewritten

QSM-01082: Joining materialized view, TEST_MV, with table, TEST, not possible

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

QSM-01102: materialized view, TEST_MV, requires join back to table, TEST, on column, FILE_ID

注(1)：QSM-01209的意思是，Oracle使用文本匹配语法发现，要查询的语句与物化视图TEST_MV是相符合的，所以进行了语句重写。

注(2)：可以使用rewrite_table;里面的其他字段得到对应的信息。

（5）当SQL语句涉及数据字典时，不能重写。

21:29:06 ELAINE@ENMOEDU > conn / as sysdba

Connected.

create materialized view test_sys

as

select d.file_name,d.file_id,d.TABLESPACE_NAME

from dba_data_files d,dba_tablespaces t

21:32:42   5  where d.TABLESPACE_NAME=t.TABLESPACE_NAME;

Materialized view created.

21:32:43 SYS@ENMOEDU > exec DBMS_MVIEW.EXPLAIN_REWRITE ('select d.file_name,d.file_id,d.TABLESPACE_NAME from dba_data_files d,dba_tablespaces t where d.TABLESPACE_NAME=t.TABLESPACE_NAME');

PL/SQL procedure successfully completed.

21:34:21 SYS@ENMOEDU > select message from rewrite_table;

MESSAGE

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

QSM-01150: query did not rewrite

QSM-01263: query rewrite not possible when query references a dictionary table or view