QUERY_REWRITE_INTEGRITY - 数据库编程

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

QUERY_REWRITE_INTEGRITY(四)