QUERY_REWRITE_INTEGRITY
QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.
Values:
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.
实验：
TOM书上的例子，我自己做一些实验 大家一起理解
首先我设置 QUERY_REWRITE_INTEGRITY = ENFORCED
TYGER@ORCL>create table emp as select * from scott.emp;
Table created.
TYGER@ORCL>create table dept as select * from scott.dept;
Table created.
TYGER@ORCL>show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
TYGER@ORCL>
TYGER@ORCL>create materialized view emp_dept
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select dept.deptno,dept.dname,count(*)
7 from emp,dept
8 where emp.deptno=dept.deptno
9 group by dept.deptno,dept.dname
10 /
Materialized view created.
TYGER@ORCL>show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
TYGER@ORCL>set autot traceonly explain
TYGER@ORCL>select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
TYGER@ORCL>desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TYGER@ORCL>desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
总结： 之所以发生这种情况是由于知道 DEPTNO 是 DEPT 表的主键，DEPTNO 在表 EMP 中应该是 NOT NULL 的，EMP 表的 DEPTNO 应该是基于 DEPT 表 DEPTNO 列的外键，但是由于我们的表没有这个外键约束存在，而 query_rewrite_integrity 又设置为 enforced ，优化器为了得到完整可靠的数据不得不查询基表而得出最终结果。
假如我们加上上面的规则，再看查询结果：
TYGER@ORCL>alter table dept add constraint dept_pk primary key(deptno);
Table altered.
TYGER@ORCL>desc dept
Name Null? Type
----------------------