DBMS_ADVANCED_REWRITE

DBMS_ADVANCED_REWRITE

개념 :
-Oracle 10g 의 새기능인 DBMS_ADVANCED_REWRITE는 특정 SQL 을 다른 SQL 로 대체하는
기능이다.
-SQL 을 바꾸지 않고 튜닝해야하는 경우에 적합하다.
-Oracle 9i 에 Stored OutLine 이라는 기능이 있었지만 위와 같은경우에는 쓰기가 매우 불편하였다.

사용방법 :
1)user 에게 권한을 준다
CONN sys/password AS SYSDBA
GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;
GRANT CREATE MATERIALIZED VIEW TO test;

2) rewrite 될 SQL
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected.
SQL>

3) 뷰를 하나만들고 쿼리가 들어오면 뷰로 대신한다.
CREATE OR REPLACE VIEW rewrite_v AS
SELECT id, INITCAP(description) AS description
FROM test_tab
ORDER BY description;

BEGIN

SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence ( name => 'test1_rewrite',

source_stmt => 'SELECT * FROM test_tab',

destination_stmt => 'SELECT * FROM rewrite_v',

validate => FALSE,

rewrite_mode => 'TEXT_MATCH');

END;/

4) 이제 설정이 끝났으므로 기능이 되는지 TEST 한다.
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected

기능이 실행되지 않았는데 이유는 아래 파라미터 때문이다.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Session altered.

SELECT * FROM test_tab;

ID DESCRIPTION
---------- --------------------------------------------------
2 Birmingham
1 Glasgow
3 London

3 rows selected.

성공적으로 수행되었다.

5)DBMS_ADVANCED_REWRITE 기능의 모든 설정을 보려면 아래뷰들을 참조하면 된다.
-DBA_REWRITE_EQUIVALENCES
-ALL_REWRITE_EQUIVALENCES
-USER_REWRITE_EQUIVALENCES

-SELECT * FROM user_rewrite_equivalences;

6) 이기능을 비활성화 하려면 아래 프로시져를 사용한다.
EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test1_rewrite');

편집후기:
DBMS 과학자 조동욱님에 따르면 바인드 변수가 있으면 기능이 수행되지 않는다고 한다.