【dbms包】DBMS_ADVANCED_REWRITE

DBMS_ADVANCED_REWRITE

 

使用这个包之前需要赋予权限:

grant execute on DBMS_ADVANCED_REWRITE to test;

grant create MATERIALIZED VIEW TO test;

 

sys用户不允许查询重写(?)

 

test用户下操作:

测试sys.dbms_advanced_rewrite.declare_rewrite_equivalence

 

Create table t_test_a(col1 varchar2(10));

Insert into t_test_a values ('A');

 

create table t_test_b(col1 varchar2(10));

insert into t_test_b values('B');

 

commit;

 

SQL> variable B1 varchar2(1)

SQL> exec :B1 := 'A';

 

PL/SQL procedure successfully completed

B1

---------

A

SQL> SELECT col1 FROM t_test_a WHERE col1 = :B1;

 

COL1

----------

A

B1

---------

A

 

SQL>

 

修改两个查询重写的参数:

SQL> alter session set query_rewrite_enabled=TRUE;

 

Session altered

 

SQL>

 

SQL> alter session set query_rewrite_integrity=TRUSTED;

 

Session altered

 

SQL>

 

执行查询重写的过程:

SQL>

SQL> begin

  2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(

  3  name => 'DUMMY2',

  4  source_stmt => 'SELECT col1 FROM t_test_a',

  5  destination_stmt => 'SELECT col1 FROM t_test_b',

  6  validate => FALSE,

  7  rewrite_mode => 'GENERAL'

  8  );

  9  end;

 10  /

 

PL/SQL procedure successfully completed

 

使用原sql查询,则被重写为了t_test_b,故而结果集没有行:

SQL> SELECT col1 FROM t_test_a WHERE col1 = :B1;

 

COL1

----------

B1

---------

A

 

SQL>

 

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

转载于:http://blog.itpub.net/16179598/viewspace-722207/