-- Sample SQL script to test View vs Materialized View
--
--  First test uses normal view  (_v)
--  Second test uses materialized view  (_m)
--
-- Drop views and index
--
  drop index dept_summary_m_idx;
  drop view dept_summary_v;
  drop materialized view dept_summary_m;
-- Create views and index
  create view dept_summary_v
	as
	  select dept.deptno
                 ,dname
                 ,count(*) nbr_emps
                 ,sum(nvl(sal,0)) tot_sal
	        from many_emps emp
                ,dept dept
	        where emp.deptno(+) = dept.deptno
	        group by dept.deptno,dname;
  create materialized view dept_summary_m
          tablespace adv_oraclass_space
	  refresh start with sysdate next sysdate + 1
	as
	  select dept.deptno
                 ,dname
                 ,count(*) nbr_emps
                 ,sum(nvl(sal,0)) tot_sal
	        from many_emps emp
                ,dept dept
	        where emp.deptno(+) = dept.deptno
	        group by dept.deptno,dname;
  create unique index dept_summary_m_idx
     on dept_summary_m (deptno)
     tablespace adv_oraclass_idx_space;
--
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
-- turn on SQL*Plus tracing tool
  set autotrace on
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
  select deptno,dname,nbr_emps,tot_sal
      from dept_summary_m
      where deptno in (10,30)
      order by deptno;
--
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
  select deptno,dname,nbr_emps,tot_sal
      from dept_summary_v
      where deptno in (10,30)
      order by deptno;
-- turn on SQL*Plus tracing tool
  set autotrace on
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
  select deptno,dname,nbr_emps,tot_sal
      from dept_summary_m
      where deptno in (10,30)
      order by deptno;
