淺析query_rewrite_integrity引數
//////////////////////
// 構造測試資料
/////////////////////
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 淺析SQL Server效能分析引數SQLServer
- 淺析MySQL語句優化中的explain引數MySql優化AI
- MySQL引數對比淺析(r11筆記第97天)MySql筆記
- JavaScript變數儲存淺析(一)JavaScript變數
- JavaScript變數儲存淺析(二)JavaScript變數
- shell指令碼中main函式中$#獲取不到指令碼傳入引數個數淺析指令碼AI函式
- iOS Block淺淺析iOSBloC
- 淺談PHP-FPM引數PHP
- Java中方法引數傳遞詳析Java
- 淺析 JWTJWT
- MongoDB淺析MongoDB
- RunLoop 淺析OOP
- Nginx淺析Nginx
- 淺析 requestAnimationFramerequestAnimationFrame
- 淺析PromisePromise
- 淺析GitGit
- 淺析RedisRedis
- Jvm 淺析JVM
- 淺析遊戲中的教程引導和章節連結遊戲
- 國產資料庫達夢資料庫(DM7)例項初始化引數淺析資料庫
- Webpack 原理淺析Web
- css: clip淺析CSS
- 淺析Promise原理Promise
- BTrace 原理淺析
- 淺析Java NIOJava
- 字串模板淺析字串
- 淺析 ReentrantLockReentrantLock
- HTTP Cache 淺析HTTP
- 淺析JAVA反射Java反射
- setXfermode 模式淺析模式
- AQS原理淺析AQS
- koa原理淺析
- CGLib淺析CGLib
- 淺析DES原理
- ejs 淺析JS
- AIDL淺析AI
- Fragment流程淺析Fragment
- 淺析SELinuxLinux