Nov 182013
 

Something I spotted on the Oracle-L mailing list the other day:

drop materialized view 
  and_keep_the_results_you preserve table;

For a script, keep reading.

create table to_aggregate as
  select round(dbms_random.value(1, 100)) 
             of_this, 
         mod(rownum, 10) this
  from   dual
  connect by level <= 100;

create materialized view 
    and_keep_the_results_you as
  select this, sum(of_this)
  from   to_aggregate
  group  by this;

-- now you see it
select count(*)
from   user_mviews
where  mview_name = 
           'AND_KEEP_THE_RESULTS_YOU';

drop materialized view 
  and_keep_the_results_you preserve table;

-- now you see now you don't
select count(*)
from   user_mviews
where  mview_name = 
           'AND_KEEP_THE_RESULTS_YOU';

-- but the data are still there!
select * 
from   and_keep_the_results_you;

 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)