利用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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-1247732/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-1247732/