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-1318535/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-1318535/