-- An excellent example of a materialized view can be found here:
-- http://www.wheresgeorge.com
-- You will notice at the bottom of the page a query that is updated
-- once every 30 minutes. Why? because that query is very expensive, too 
-- expensive to be run every time someone loads the homepage. Thus, 
-- it is better to use a materialized view.


create materialized view
MV_TOTALS
BUILD IMMEDIATE
REFRESH COMPLETE
enable query rewrite
as
select count(rowid) "PASTAS", sum(cheese) "TOTAL_CHEESE" from pasta
/ 

 -- USE THE DBMS_JOB package to call the DBMS_MVIEW package once every so often
 -- to update the materialized view!
 --
 --   Parameters for dbms_job.submit:
 --   DBMS_JOB.SUBMIT( JOB OUT BINARY_INTEGER,
 --                    WHAT IN VARCHAR2, 
 --                    NEXT_DATE IN DATE DEFAULT SYSDATE,
 --                    INTERVAL IN VARCHAR2 DEFAULT 'NULL',
 --                    NO_PARSE IN BOOLEAN DEFAULT FALSE,
 --                    INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
 --                    FORCE IN BOOLEAN DEFAULT FALSE) 

declare
jobno number;
-- variable instno number;
begin
dbms_job.submit(:jobno, 'dbms_mview.refresh(''mv_totals'',''C'');', 
trunc(sysdate+(1/24/12),'MI'), 'trunc(SYSDATE+(1/24/6),''MI'')'      );
 -- do this in 5 minutes, then every 10 minutes
commit;  -- is this commit necessary? I doubt it, but Oracle does it.
end;
/ 

prompt The job is the number assigned to this task. It can be found in the user_jobs view.
prompt  the job:
print jobno 
prompt
prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt Make sure that the parameter job_queue_processes is set to a number greater than 0.
show parameter job_queue_processes
prompt 
prompt
prompt  Next scheduled run
prompt  ~~~~~~~~~~~~~~~~~~
prompt  The next scheduled run for this job is:
select job, next_date, next_sec from user_jobs where job = :jobno;