淺析query_rewrite_integrity引數

oliseh發表於2015-08-27

//////////////////////
// 構造測試資料
/////////////////////

create table scott.testp1 (c1 varchar2(10),d1 varchar2(10));
create table scott.testf1 (id11 number,c11 varchar2(10));
insert into scott.testp1 values('a','AAA');
insert into scott.testp1 values('b','BBB');
insert into scott.testp1 values('c','CCC');
alter table scott.testp1 add constraint pk_testp1_c1 primary key(c1) using index tablespace users;

insert into scott.testf1 values(1,'a');
insert into scott.testf1 values(2,'b');
insert into scott.testf1 values(3,'b');
insert into scott.testf1 values(3,'c');

alter table scott.testf1 add constraint fk_testf1_c11 foreign key(c11) references scott.testp1(c1);


create materialized view scott.mvtest build immediate refresh on demand enable query rewrite as select testp1.d1,sum(testf1.id11) from scott.testp1,scott.testf1 where c1=c11 group by d1;


SQL> select * from scott.mvtest;

D1                   SUM(TESTF1.ID11)
-------------------- ----------------
BBB                                 5
AAA                                 1
CCC                                 3


alter table scott.testf1 modify (c11 not null);  <---修改外來鍵為not null,引導optimizer使用query rewrite

////////////////////////
//query_rewrite_integrity=ENFORCED或STALE_TOLERATED的情況下, constraint必須處於enable狀態
//才能用上query   rewrite

////////////////////////

SQL> show parameter query_rewrite_integrity

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 enforced


SQL> select sum(id11) from scott.testf1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3317803245

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MVTEST |     3 |    39 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

alter table scott.testf1 disable constraint fk_testf1_c11;


SQL> select sum(id11) from scott.testf1;


Execution Plan
----------------------------------------------------------
Plan hash value: 222374761

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TESTF1 |     4 |    52 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> alter system set query_rewrite_integrity=stale_tolerated scope=memory;


System altered.


SQL> show parameter query_rewrite_integrity;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 STALE_TOLERATED


SQL> select sum(id11) from scott.testf1;


Execution Plan
----------------------------------------------------------
Plan hash value: 222374761

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TESTF1 |     4 |    52 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

alter table scott.testf1 enable constraint fk_testf1_c11;


SQL> show parameter query_rewrite_integrity;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 STALE_TOLERATED


SQL> select sum(id11) from scott.testf1;


Execution Plan

----------------------------------------------------------
Plan hash value: 3317803245

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MVTEST |     3 |    39 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


//////////////////////

// rely disable constraint的情況下只有query_rewrite_integrity=trusted 才能用上Query rewrite
//
/////////////////////

alter system set query_rewrite_integrity=enforced scope=memory;


SQL> alter table scott.testf1 modify constraint fk_testf1_c11 rely disable;


Table altered.


###rely disable constraint情況下:ENFORCED和STALE_TOLERATED均無法使用查詢重寫

SQL> show parameter query_rewrite_integrity;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 ENFORCED


SQL> select sum(id11) from scott.testf1;


Execution Plan
----------------------------------------------------------
Plan hash value: 222374761

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TESTF1 |     4 |    52 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL>  alter system set query_rewrite_integrity=stale_tolerated scope=memory;


System altered.


SQL> show parameter query_rewrite_integrity;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 STALE_TOLERATED


SQL> select sum(id11) from scott.testf1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3317803245

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MVTEST |     3 |    39 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


###rely disable constraint情況下:TRUSTED才能使用查詢重寫

SQL> alter system set query_rewrite_integrity=trusted scope=memory;


System altered.


SQL> show parameter query_rewrite_integrity;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_integrity              string                 TRUSTED


SQL> select sum(id11) from scott.testf1;


Execution Plan

----------------------------------------------------------
Plan hash value: 3317803245

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MVTEST |     3 |    39 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

總結:
QUERY_REWRITE_INTEGRITY=STALE_TOLERATED雖然對於MV內容是否stale沒有要求,但在對各種約束的完整性檢查方面與ENFORCED遵循同樣嚴格的標準;
QUERY_REWRITE_INTEGRITY=TRUSTED能夠信任被標示為rely的約束,無論該約束是否enabled

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/53956/viewspace-1784273/,如需轉載,請註明出處,否則將追究法律責任。