利用query_rewrite調優SQL效能甚至改變結果

1，問題：
一般為了解決效能問題，可能需要使用outline、sql profile固定執行計劃。
dbms_advanced_rewrite重寫SQL，甚至可能透過重寫改變查詢結果。

2，條件：
--許可權：
execute on dbms_advanced_rewrite
create materialized view

--回話或系統級設定
query_rewrite_integerity=trusted/stale_tolerated(enfored為預設值)

 

3，測試：
3.1版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

3.2準備使用者
SQL> create user tom identified by tom;
User created.

SQL> grant connect,resource to tom;
Grant succeeded.

SQL> grant execute on dbms_advanced_rewrite to tom;
Grant succeeded.

 

3.3 準備測試表：
SQL> create table t1(x int);
Table created.

SQL> create table t2(x int);
Table created.

SQL> insert into t1 values(1);
1 row created.

SQL> r
  1* insert into t1 values(1)
1 row created.

SQL> commit;
Commit complete.

3.4 宣告等價性
begin
  SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
   name             => 'TOM.SQL1',
   source_stmt      => 'select * from t1',
   destination_stmt => 'select * from t2',
   validate         =>false);
end;
/

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

SQL> conn / as sysdba
SQL> grant create materialized view to tom;
Grant succeeded.

SQL> conn tom/tom
SQL> begin
  SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
   name             => 'TOM.SQL1',
   source_stmt      => 'select * from t1',
   destination_stmt => 'select * from t2',
   validate         =>false);
end;
/
  2    3    4    5    6    7    8 
PL/SQL procedure successfully completed.

3.5 看效果
SQL> select * from t1;
         X
----------
         1
         1

--需要設定查詢重寫完整性
SQL> alter session set query_rewrite_integrity=trusted;
Session altered.

SQL> select * from t1;
no rows selected

--變為大寫，不妨礙重寫
SQL> select * from T1;
no rows selected

--增加空格，不妨礙重寫
SQL> select * from  T1;
no rows selected

--增加別名，影響重寫
SQL> select * from t1 a;
         X
----------
         1
         1