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.
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; /
SQL> ALTER SESSION SET query_rewrite_integrity = trusted; Session altered. SQL> select ename from emp; ENAME ---------- Forget it! Forget it! ...