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/，如需轉載，請註明出處，否則將追究法律責任。