Query rewrite SQL

 
create table your_app ( 
  it_does_not_require_changes integer, 
  this_query_appears varchar2(1),
  you_need_it_to_be_much_faster varchar2(1));

insert into your_app 
  select rownum, case when mod(rownum, 100) = 0 then 'Y' else 'N' end,
         case when floor(rownum/100) = 0 then 'Y' else 'N' end
  from   dual 
  connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'your_app');

explain plan for
  select it_does_not_require_changes
  from   your_app
  where  this_query_appears = 'Y'
  and    you_need_it_to_be_much_faster = 'Y';
  
select * from table(dbms_xplan.display);

create materialized view can_niftily_speed_up_sql_if_i enable query rewrite as
  select it_does_not_require_changes
  from   your_app
  where  this_query_appears = 'Y'
  and    you_need_it_to_be_much_faster = 'Y';
  
exec dbms_stats.gather_table_stats(user, 'can_niftily_speed_up_sql_if_i');
  
explain plan for
  select it_does_not_require_changes
  from   your_app
  where  this_query_appears = 'Y'
  and    you_need_it_to_be_much_faster = 'Y';
 
-- it should now use the MV, instead of the base query
-- which should help in this case 
--(though arguably, you should probably have just added some indexes!)
select * from table(dbms_xplan.display);
  
drop materialized view can_niftily_speed_up_sql_if_i ;
drop table your_app purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)