Simple example
The user of the functionality of
dbms_advanced_rewrite needs to have two
privileges. These cannot, obviously, be granted by the user receiving them:
connect / as sysdba
grant execute on dbms_advanced_rewrite to rene;
grant create materialized view to rene;
Then, the user that received the privileges, creates a table and inserts some data …
create table tq84_rewrite_tab_1 (
num number,
txt varchar2(20)
);
insert into tq84_rewrite_tab_1 values (1, 'one' );
insert into tq84_rewrite_tab_1 values (2, 'two' );
insert into tq84_rewrite_tab_1 values (3, 'three');
insert into tq84_rewrite_tab_1 values (4, 'four' );
insert into tq84_rewrite_tab_1 values (5, 'five' );
commit;
… creates a rewrite equivalency …
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
name => 'return_twice_the_num',
source_stmt => 'select num, txt from tq84_rewrite_tab_1',
destination_stmt => 'select 2*num, txt from tq84_rewrite_tab_1',
validate => false,
rewrite_mode => 'general'
);
end;
/
… and selects from the table on which the equivaleny was defined:
alter system set query_rewrite_integrity=trusted scope=memory;
alter system set query_rewrite_enabled =force scope=memory;
select
txt,
num
from
tq84_rewrite_tab_1;
select
txt,
num / 2 as num
from
tq84_rewrite_tab_1;
Cleaning up
begin
sys.dbms_advanced_rewrite.drop_rewrite_equivalence('return_twice_the_num');
end;
/
drop table tq84_rewrite_tab_1;