René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

dbms_advanced_rewrite

Procedures/Functions

alter_rewrite_equivalence

procedure alter_rewrite_equivalence (
 name                               in                 varchar2        ,
 rewrite_mode                       in                 varchar2        
);

build_safe_rewrite_equivalence

procedure build_safe_rewrite_equivalence (
 name                               in                 varchar2        ,
 source_stmt                        in                     clob        ,
 destination_stmt                   in                     clob        ,
 check_sum                          in           binary_integer        
);

declare_rewrite_equivalence

procedure 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_equivalence

procedure drop_rewrite_equivalence (
 name                               in                 varchar2        
);

validate_rewrite_equivalence

procedure 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