OCM 12c升级考试-物化视图查询重写

首先为了进行物化视图查询重写的测试,我们需要弄点测试数据,这里我们选择Oracle自带的脚本utlsampl.sql,通过运行该脚本,创建scott用户和emp、DEPT等表。这里需要注意的一点是该脚本在CDB下面是无法运行的,会报ORA-65049: creation of local user or role is not allowed in CDB$ROOT错误。

SQL> alter session set container=testdb1;
SQL> @?/rdbms/admin/utlsampl.sql
创建完成之后就可以登陆pdb了.
SQL> alter session set container=testdb1;
SQL> connect scott/tiger@testdb1
Connected.

创建的表dept和emp是一对多的关系。一个部门,可以有多个员工。所以在这里我们先加上一个约束条件,不允许emp的deptno为空。

alter table scott.emp modify (deptno not null);

接着我们还要给我们的用户scott赋予创建物化视图等权限

grant create materialized view ,select any dictionary to scott;

创建物化视图,让emp和dept通过deptno进行关联。

create materialized view MV_TEST enable query rewrite as select * from DEPT join EMP using (DEPTNO);

同时我们还需要检查查询重写的参数是否是打开的,query_rewrite_enabled设置为true则打开查询重写功能,query_rewrite_integrity为enforced,表示只有当严格符合特定约束和规则时才会对SQL语句进行查询重写至物化视图,否则的话不重写。

SQL> show parameter rewrite

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

接下来我们在emp上执行查询,使用hint /*+ rewrite_or_error */,查询应该读取物化视图而不是表,因为物化视图中包含emp中所有的行和列的数据。

SQL> select /*+ rewrite_or_error */ * from emp;
select /*+ rewrite_or_error */ * from emp
*
ERROR at line 1:
ORA-30393: a query block in the statement did not rewrite

然而我发现报了ORA-30393错误,没有能够执行重写成功。如果我们需要了解为什么查询重写不发生,我们可以使用Oracle提供的工具dbms_mview.explain_rewrite收集更多的信息。运行下面的utlxrw.sql,可以创建REWRITE_TABLE表来存储结果.

@ ?/rdbms/admin/utlxrw.sql

Rem $Header: rdbms/admin/utlxrw.sql /main/7 2011/05/20 12:21:56 traney Exp $
Rem
Rem utlxrw.sql
Rem
Rem Copyright (c) 2000, 2011, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem    NAME
Rem      utlxrw.sql - Create the output table for EXPLAIN_REWRITE
Rem
Rem    DESCRIPTION
Rem     Outputs of the EXPLAIN_REWRITE goes into the table created
Rem     by utlxrw.sql (called REWRITE_TABLE). So utlxrw must be
Rem     invoked before any EXPLAIN_REWRITE tests.
Rem
Rem    NOTES
Rem      If user specifies a different name in EXPLAIN_REWRITE, then
Rem      it should have been already created before calling EXPLAIN_REWRITE.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    traney      04/05/11 - 35209: long identifiers dictionary upgrade
Rem    desingh     10/19/05 - bug#4401918:change join_back cols lengths
Rem    mthiyaga    04/29/05 - Remove unncessary comment
Rem    mthiyaga    06/08/04 - Add rewritten_txt field
Rem    mthiyaga    10/10/02 - Add extra columns
Rem    mthiyaga    09/27/00 - Create EXPLAIN_REWRITE output table
Rem    mthiyaga    09/27/00 - Created
Rem
Rem
CREATE TABLE REWRITE_TABLE(
statement_id          VARCHAR2(30),  -- id for the query
mv_owner              VARCHAR2(128),  -- owner of the MV
mv_name               VARCHAR2(128),  -- name of the MV
sequence              INTEGER,       -- sequence no of the error msg
query                 VARCHAR2(4000),-- user query
query_block_no        INTEGER,       -- block no of the current subquery
rewritten_txt         VARCHAR2(4000),-- rewritten query
message               VARCHAR2(512), -- EXPLAIN_REWRITE error msg
pass                  VARCHAR2(3),   -- rewrite pass no
mv_in_msg             VARCHAR2(128),  -- MV in current message
measure_in_msg        VARCHAR2(30),  -- Measure in current message
join_back_tbl        VARCHAR2(4000),-- Join back table in current msg
join_back_col        VARCHAR2(4000),-- Join back column in current msg
original_cost         INTEGER,       -- Cost of original query
rewritten_cost        INTEGER,       -- Cost of rewritten query
flags                 INTEGER,       -- associated flags
reserved1             INTEGER,       -- currently not used
reserved2             VARCHAR2(10))  -- currently not used
/

执行dbms_mview.explain_rewrite存储过程,第一个参数输入执行的语句,第二个参数输入物化视图的名字。
exec dbms_mview.explain_rewrite(‘select * from EMP’,’MV_TEST’);
执行完成之后,查询REWRITE_TABLE表查看结果

SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;

SEQUENCE MESSAGE
---------- --------------------------------------------------------------------------------
1 QSM-01150: query did not rewrite
2 QSM-01219: no suitable materialized view found to rewrite this query

这个问题通过查询MOS,可以发现是Bug 17651484 ORA-30393 – query rewrite not happening in when expected for MV with ANSI join导致的,因为在前面我们也使用了ANSI join,这个Bug会影响11.2.0.4和12cR1版本的数据库。

create materialized view MV_TEST enable query rewrite as select DNAME,emp.* from DEPT,EMP where DEPT.DEPTNO=emp.DEPTNO; 
exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');
SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- --------------------------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, MV_TEST

再次执行dbms_mview.explain_rewrite存储过程,查询REWRITE_TABLE的结果,发现查询可以正常重写了。接下来,我们来测试一下参数query_rewrite_integrity的不同,带来的各种变化。首先前面我们设置的query_rewrite_integrity为enforced,表示只有当严格符合特定约束和规则时才会对SQL语句进行查询重写至物化视图,否则的话不重写。而约束它不仅仅是验证数据的完整性,它还向优化器提供信息。如果我们这个时候把外键禁用掉,我们看下还会执行重写吗?

SQL> alter table EMP disable constraint FK_DEPTNO; 
Table altered.
SQL> delete from REWRITE_TABLE; 
2 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');
PL/SQL procedure successfully completed.
SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;
  SEQUENCE MESSAGE
---------- --------------------------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01110: query rewrite not possible with materialized view MV_TEST because itcontains a join between tables (EMP and DEPT) that is not present in the 
           query and that potentially eliminates rows needed by the query
         3 QSM-01052: referential integrity constraint on table, EMP, not VALID in ENFORCED integrity mode

这里说的很清楚。在ENFORCED模式下,约束必须是要有效的。否则关联可能消除一些行。通俗一点说:假设现在外键失效,我在emp中插入了一些新的行,这些行和dept是没有任何关联。那么我在查询emp表的时候,查询重写就不会选择使用物化视图,因为物化视图是emp和dept关联的结果,关联查询结果会导致emp新插入的一些行查不到。这时优化器为了数据的准确性,最终会选择查询基本表emp。
那么如果我把约束设置成rely模式呢?
有时候我们自己可以确认在表emp和dept下面关联数据是一致的,但是又不想建约束,因为约束会限制DML和Load的性能。虽然表中的数据是一致性的,不设置约束的情况下Oracle优化器并不知道。为了告诉优化器数据是符合一致性约束的,我们可以把约束设置成rely模式。但本身约束它仍然是disable的。

alter table EMP modify constraint FK_DEPTNO rely; 
delete from REWRITE_TABLE;
exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');

SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ----------------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01110: query rewrite not possible with materialized view MV_TEST because it contains a join between tables (EMP and DEPT) that is not present in the 
           query and that potentially eliminates rows needed by the query
         3 QSM-01052: referential integrity constraint on table, EMP, not VALID in ENFORCED integrity mode

可以看到这样做还是不行,这是因为参数仍然是ENFORCED模式,我们必须把query_rewrite_integrity参数设置成trusted。也就是信任。

SQL> alter system set query_rewrite_integrity=trusted;
System altered.
SQL> exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;
  SEQUENCE MESSAGE
---------- ----------------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, MV_TEST

这里可以看到我们把参数query_rewrite_integrity设置成了trusted,优化器就可以执行查询重写到物化视图了。
那么假设我现在往emp表中插入一条数据会是什么情况呢?

insert into EMP (EMPNO,ENAME,DEPTNO) values (777,'buddy',50);
delete from REWRITE_TABLE;
SQL> exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;
  SEQUENCE MESSAGE
---------- ----------------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01031: materialized view, MV_TEST, is stale in TRUSTED integrity mode

可以看到查询重写又失败了,这个是因为我们的emp表已经更新了,而物化视图没有更新,物化视图查询出来的数据是陈旧的数据。如果我们仍然需要查询重写,我们需要降低查询重写的安全级别。

alter session set query_rewrite_integrity=stale_tolerated;
delete from REWRITE_TABLE;
SQL> exec dbms_mview.explain_rewrite('select * from EMP','MV_TEST');
SQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;
  SEQUENCE MESSAGE
---------- ----------------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, MV_TEST

降低了安全级别之后,查询重写生效了,但是现在查询的结果集不会显示刚刚我们插入的那条记录

结论
查询重写取决于约束的完整性。当query_rewrite_integrity =enforced,完整性必须由oracle执行(约束经过验证并启用)。当query_rewrite_integrity=trusted时,用户自己对数据完整性负责,并控制重写是否可以依赖约束,当query_rewrite_integrity=stale_tolerated,可以容忍查询陈旧的物化视图。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569