ion | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 16 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
而如果设置 query_rewrite_integrity = trusted 的话，那么优化器因为有了我们上述那个 novalidate约束的误导，它并不真实检验数据的完整性，因此还将会利用物化视图查询重写，尽管这样得出的结果是错误的。只要我们让优化器知道有完整性约束的存在，不管约束严格与否，优化器只要为了这个信息就会尽可能地利用物化视图查询重写。
TYGER@ORCL>alter session set query_rewrite_integrity=trusted;
Session altered.
TYGER@ORCL>select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 155013515
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)|00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | ||
| 2 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT | 3 | 39 | 3 (0)|00:00:01 |
--------------------------------------------------------------------------------
----------
Note
-----
- dynamic sampling used for this statement
而 stale_tolerated 就简单了：
/* 结果 我自己做实验却没有得到的想要的结果： */
TYGER@ORCL>show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
// session 级别修改参数
TYGER@ORCL>alter session set query_rewrite_integrity=stale_tolerated;
Session altered.
TYGER@ORCL>show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string STALE_TOLERATED
TYGER@ORCL>set autot traceonly explain;
TYGER@ORCL>select count(*) from emp;
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
// system 级别修改参数 仍然如此
TYGER@ORCL>alter system set query_rewrite_integrity=stale_tolerated;
System altered.
TYGER@ORCL>set autot traceonly explain;
TYGER@ORCL>select count(*) from emp;
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>conn / as sysdba
Connected.
SYS@ORCL>shutdown immediate
Database closed.
Database dismounte |