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

   在Oracle的Query Rewrite中主要有三点, 第一是要使用CBO; 第二是要设置query rewrite enabled参数为TRUE; 第三是要先择设置query rewrite integrity参数的值(stale_tolerated, trusted, enforced). 对于第一点, 我们最好analyze相关的表及索引及MV; 对于第二点,这个参数只有两个值(true, false), 很简单; 对于第三点, 我们先来看Oracle的官方对于这个参数的解释:

ENFORCED
   Oracle enforces and guarantees consistency and integrity

TRUSTED
   Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.

STALE_TOLERATED
   Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

   这个参数有点难于理解一些, 但主要和数据的一致性有关, 在Oracle的Query Rewrite中, 一些约束的声明或状态和Oracle决于可否Query Rewrite有很大的关系. ENFORCED表示Oracle只相信Enabled和Validated的约束, 而Trusted则相信RELY的约束, 就算这个约束没有Enabled和Validated, 这两种都要求MVIEW中的数据是及时刷新的,而STALE_TOLERATED则可以容忍一切, 就算中间表的数据是旧的, 指基表有新数据修改而MVIEW还没有刷新的情况下, Oracle也会选择使用Query Rewrite来作查询, 在这种情况下, 查出来的数据可能是不准的. 下面我们来作一个例子来显示enforced与trusted的不同:  

接着前面的例子,我们创建这样一个实体化视图:
CREATE MATERIALIZED VIEW MV_TABLE
ENABLE QUERY REWRITE
AS
SELECT U.USER#,COUNT(*) OBJCNT FROM USR_TABLE U,OBJ_TABLE O
WHERE U.USER#=O.USER#
group by u.user#

接下来我们创建这样的两个约束:
ALTER TABLE USR_TABLE ADD PRIMARY KEY (USER#) RELY DISABLE;
ALTER TABLE OBJ_TABLE ADD FOREIGN KEY (USER#) 
    REFERENCES USR_TABLE(USER#) RELY DISABLE;

下接来创建一个USR_LEVEL的表, 如下所示:
CREATE TABLE USR_LEVLEL AS SELECT USER#, TRUNC(USER#/10) ULEVEL FROM USR_TABLE;

  实验所需要的表都建起来了, 对三个表和一个MVIEW进行分析后, 下面来做测试:

SQL> SHOW PARAMETER QUERY_REWRITE

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

SQL> SET AUTOT ON EXP
SQL> select l.ulevel,count(*) from usr_level l,obj_table o
  2  where o.user#=l.user#
  3  group by l.ulevel;

    ULEVEL   COUNT(*)
---------- ----------
         0     101456
         1        136
         2       9939

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 ...
   1    0   SORT (GROUP BY) (Cost=7 ...
   2    1     HASH JOIN (Cost=5 ...
   3    2       TABLE ACCESS (FULL) OF 'MV_TABLE' (Cost=2 ...
   4    2       TABLE ACCESS (FULL) OF 'USR_LEVEL' (Cost=2 ...

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED;

Session altered.

SQL> select l.ulevel,count(*) from usr_level l,obj_table o
  2  where o.user#=l.user#
  3  group by l.ulevel;

    ULEVEL   COUNT(*)
---------- ----------
         0     101456
         1        136
         2       9939

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3239 ...
   1    0   SORT (GROUP BY) (Cost=3239 ...
   2    1     HASH JOIN (Cost=50 ...
   3    2       TABLE ACCESS (FULL) OF 'USR_LEVEL' (Cost=2 ...
   4    2       TABLE ACCESS (FULL) OF 'OBJ_TABLE' (Cost=47 ...

    通过对比Cost和Plan可以看出那个Plan更好.