DBMS ADVANCED REWRITE

DBMS_ADVANCED_REWRITE is a PL/SQL package that allows you to transform queries on the fly. This is mostly useful for query tuning. For example, it allows you to optimized an application's SQL without modifying the application.

Examples

Enable advanced rewrite for a user:

GRANT execute ON sys.dbms_advanced_rewrite TO scott;
GRANT create materialized view TO scott;

Modify a query:

connect scott/tiger
BEGIN
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
    'new_query',
    'select ename from emp',
    'select ''Forget it!'' from emp',
    false);
END;
/

Test it:

SQL> ALTER SESSION SET query_rewrite_integrity = trusted;
Session altered.

SQL> select ename from emp;
ENAME
----------
Forget it!
Forget it!
...