首页 > 代码库 > QUERY_REWRITE_INTEGRITY

QUERY_REWRITE_INTEGRITY

QUERY_REWRITE_INTEGRITY

PropertyDescription
Parameter typeString
SyntaxQUERY_REWRITE_INTEGRITY = { enforced | trusted | stale_tolerated }
Default valueenforced
ModifiableALTER SESSIONALTER SYSTEM
Real Application ClustersMultiple instances can have different values.

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
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                    NOT NULL NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)


     TYGER@ORCL>alter table emp
      2  add constraint emp_fk_dept
      3  foreign key(deptno) references dept(deptno);


    Table altered.


    TYGER@ORCL>alter table emp modify deptno not null;


    Table altered.


    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                                    NOT NULL NUMBER(2)


     TYGER@ORCL>set autot traceonly explain
    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






    可见 当我们将完整性约束全部添加后,优化器有了足够的信息知道这条语句通过物化视图可以查询重写,而不用查询基表,实际的执行情况也印证了这一点。




    我们在通过一个例子说明 THUSTED 的情况。我们首先将这些约束去除掉,然后添加一行新的数据


    TYGER@ORCL>alter table emp drop constraint emp_fk_dept;


    Table altered.


    TYGER@ORCL>alter table dept drop constraint dept_pk;


    Table altered.


    TYGER@ORCL>alter table emp modify deptno null;


    Table altered.


    TYGER@ORCL>desc dept 
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                             NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)


    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>insert into emp(empno,deptno) values(1,1);


    1 row created.


    TYGER@ORCL>exec dbms_mview.refresh(‘EMP_DEPT‘);


    PL/SQL procedure successfully completed.


    TYGER@ORCL>alter materialized view emp_dept consider fresh;
    alter materialized view emp_dept consider fresh
            *
    ERROR at line 1:
    ORA-30374: materialized view is already fresh


     
     // 创建一个 novalidate 的约束
    TYGER@ORCL>alter table dept
      2  add constraint dept_pk primary key(deptno)
      3  rely enable novalidate
      4  /


    Table altered.


    TYGER@ORCL>alter table emp
      2  add constraint emp_fk_dept
      3  foreign key(deptno) references dept(deptno)
      4  rely enable novalidate
      5  /


    Table altered.


    TYGER@ORCL>alter table emp modify deptno not null novalidate;


    Table altered.


    回到原来的查询,假如 query_rewrite_integrity = enforced 的话,那么我们知道由于上述约束实际上是违反真实数据约束的,因此优化器将不会利用物化视图查询重写。


    TYGER@ORCL>show parameter query


    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    query_rewrite_enabled                string      TRUE
    query_rewrite_integrity              string      enforced
    TYGER@ORCL>
    TYGER@ORCL>set autot traceonly explain
    TYGER@ORCL>select count(*) from emp;


      COUNT(*)
    ----------
            16




    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  |    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 dismounted.
    ORACLE instance shut down.
    SYS@ORCL>startup
    ORACLE instance started.


    Total System Global Area  285212672 bytes
    Fixed Size                  1218992 bytes
    Variable Size              71304784 bytes
    Database Buffers          209715200 bytes
    Redo Buffers                2973696 bytes
    Database mounted.
    Database opened.
    SYS@ORCL>conn tyger/tyger
    Connected.
    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




    虽然结果有点不是很满意,但是看完后基本可以了解  query_rewrite_integrity 这个参数了吧 




    遗留问题:356 行 stale_tolerated 参数设置,    如果看出错误的步骤 欢迎指正。