ITPub博客

首页 > 数据库 > Oracle > 利用query_rewrite调优SQL性能甚至改变结果

利用query_rewrite调优SQL性能甚至改变结果

原创 Oracle 作者:redhouser 时间:2014-08-08 11:26:12 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle-wallet
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    805975