-- DBMS_ADVANCED_REWRITE demo - after Oracle 10g -
conn / as sysdba

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO hr ;

GRANT CREATE MATERIALIZED VIEW TO hr ;

GRANT ALTER SESSION TO hr ;

conn hr/hr

select first_name name from employees where employee_id = 100 ;

NAME
--------------------
Steven

select department_name name from departments where department_id = 10 ;

NAME
------------------------------
Administration

DECLARE
  sclob CLOB;
  dclob CLOB;
BEGIN
  sclob := CAST('select first_name name from employees where employee_id = 100' AS CLOB);
  dclob := CAST('select department_name name from departments where department_id = 10' AS CLOB);
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence('TST', sclob, dclob, FALSE);
END;
/

select first_name name from employees where employee_id = 100 ;

NAME
--------------------
Steven

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

select first_name name from employees where employee_id = 100 ;

NAME
------------------------------
Administration

SELECT * FROM user_rewrite_equivalences;

OWNER                          NAME
------------------------------ ------------------------------
SOURCE_STMT
--------------------------------------------------------------------------------
DESTINATION_STMT
--------------------------------------------------------------------------------
REWRITE_MO
----------
HR                             TST
select first_name name from employees where employee_id = 100
select department_name name from departments where department_id = 10
TEXT_MATCH

exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence('TST');