René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

dbms_advanced_rewrite  
Procedures/Functionsalter_rewrite_equivalenceprocedure alter_rewrite_equivalence ( name in varchar2 , rewrite_mode in varchar2 ); build_safe_rewrite_equivalenceprocedure build_safe_rewrite_equivalence ( name in varchar2 , source_stmt in clob , destination_stmt in clob , check_sum in binary_integer ); declare_rewrite_equivalenceprocedure declare_rewrite_equivalence ( name in varchar2 , source_stmt in clob , destination_stmt in clob , validate in boolean default, rewrite_mode in varchar2 default ); drop_rewrite_equivalenceprocedure drop_rewrite_equivalence ( name in varchar2 ); validate_rewrite_equivalenceprocedure validate_rewrite_equivalence ( name in varchar2 ); Links
dba_rewrite_equivalences can be used to find all
stated rewrite equivalences.
Example
The following example was created as user rene:
create table rewrite_example ( num number, txt varchar2(10) ); insert into rewrite_example values (1, 'one' ); insert into rewrite_example values (2, 'two' ); insert into rewrite_example values (3, 'three'); insert into rewrite_example values (4, 'four' ); insert into rewrite_example values (5, 'five' ); begin sys.dbms_advanced_rewrite.declare_rewrite_equivalence ( 'return_twice_the_num', 'select num, txt from rene.rewrite_example', 'select 2*num, txt from rene.rewrite_example', false); end; / alter system set query_rewrite_integrity = trusted; alter system set query_rewrite_enabled = force; select num from rene.rewrite_example; NUM  2 4 6 8 10 