REM *******************************************************************************************
REM * Revision History           Magic of Materialized Views                                  *
REM *******************************************************************************************
REM Revision    Date(DD/MM/YYYY) Changed by         Remarks
REM *******************************************************************************************
REM Rev 1.00    21/01/2007       Bhupinder Singh    Created
REM                                                 This script is created to test materialized
REM                                                 views.
REM Rev 1.01    15/10/2007       Bhupinder Singh    Added Execution plan to test performance of
REM                                                 Materialized views from SQL*Plus.
REM
REM ** I have taken reference from Oracle site and various other sources like internet and 
REM ** friends to write this script for performance testing of Materialized Views.
REM ** This script will take some time to create big table for Materialized View. Make sure you
REM ** have enough tablespace for the schema to accomodate big table.
REM *******************************************************************************************

SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 20000
SET LINESIZE 2000
SET TRIMSPOOL ON
SET AUTOTRACE OFF
SET TIMING OFF

ACCEPT file_and_path CHAR prompt 'Enter File Name and path for LOG file (Default:D:\BHs_MV_LOG.log) : '
COLUMN file_path new_value file_path2 noprint

SELECT NVL( '&file_and_path','D:\BHs_MV_LOG.log') file_path
FROM   DUAL;

SPOOL &file_path2

PROMPT-->Important Note:
PROMPt-- There is one mandatory INIT.ORA parameter necessary for 
PROMPT-- materialized views to function, this is the COMPATIBLE parameter. 
PROMPt-- The value of COMPATIBLE should be set to 8.1.0, or above, for 
PROMPT-- query rewrites to be functional. If this value is not set 
PROMPT-- appropriately, query rewrite will not be invoked. 
PROMPT
PROMPT --> Creating objects to test Materialized Views

CREATE SEQUENCE x3_bhs_mv_seq START WITH 1 INCREMENT BY 1
/
CREATE TABLE x3_bhs_mv_big_tab nologging 
    AS SELECT x3_bhs_mv_seq.NEXTVAL obj_no,owner,table_name,column_name FROM all_tab_columns
/
COMMIT
/
INSERT /*+ APPEND */ INTO x3_bhs_mv_big_tab 
SELECT x3_bhs_mv_seq.NEXTVAL,
       owner,
       table_name,
       column_name 
FROM x3_bhs_mv_big_tab
/
COMMIT
/
INSERT /*+ APPEND */ INTO x3_bhs_mv_big_tab 
SELECT x3_bhs_mv_seq.NEXTVAL,
       owner,
       table_name,
       column_name 
FROM x3_bhs_mv_big_tab
/
COMMIT
/
INSERT /*+ APPEND */ INTO x3_bhs_mv_big_tab 
SELECT x3_bhs_mv_seq.NEXTVAL,
       owner,
       table_name,
       column_name 
FROM x3_bhs_mv_big_tab
/
COMMIT
/
INSERT /*+ APPEND */ INTO x3_bhs_mv_big_tab 
SELECT x3_bhs_mv_seq.NEXTVAL,
       owner,
       table_name,
       column_name 
FROM x3_bhs_mv_big_tab
/
COMMIT
/
INSERT /*+ APPEND */ INTO x3_bhs_mv_big_tab 
SELECT x3_bhs_mv_seq.NEXTVAL,
       owner,
       object_id,
       object_name 
FROM   all_objects
/
COMMIT
/

PROMPT--> Computing statistics on table x3_bhs_mv_big_tab

ANALYZE TABLE x3_bhs_mv_big_tab compute statistics
/

PROMPT--> Now the table x3_bhs_mv_big_tab has following rows

SELECT COUNT(*) FROM x3_bhs_mv_big_tab
/
PROMPT--> Creating Materialized View Log

CREATE MATERIALIZED VIEW LOG ON x3_bhs_mv_big_tab
WITH ROWID
/
PROMPT--> Creating Materialized View of x3_bhs_mv_test table

CREATE MATERIALIZED VIEW x3_bhs_mv_view
PARALLEL
BUILD IMMEDIATE 
REFRESH ON COMMIT
AS
SELECT COUNT(a.obj_no) mv_cnt_col1,
       a.owner mv_col2,
       a.table_name mv_col3,
       SUM(a.obj_no) mv_sum_col4
FROM   x3_bhs_mv_big_tab a
GROUP BY a.owner, a.table_name
/

PROMPT--> Creating Normal View of x3_bhs_normal_view table for comparision

CREATE OR REPLACE VIEW x3_bhs_normal_view
AS 
SELECT COUNT(a.obj_no) mv_cnt_col1,
       a.owner mv_col2,
       a.table_name mv_col3,
       SUM(a.obj_no) mv_sum_col4
FROM   x3_bhs_mv_big_tab a
GROUP BY a.owner, a.table_name
/
PROMPT --> Program to test Materialized View

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_start_time VARCHAR2(50);
v_end_time   VARCHAR2(50);
v_dummy      NUMBER;
CURSOR c1 IS 
       SELECT * 
       FROM    x3_bhs_normal_view;
CURSOR c2 IS 
       SELECT * 
       FROM    x3_bhs_mv_view;
BEGIN
   DBMS_OUTPUT.PUT_LINE('>');
   DBMS_OUTPUT.PUT_LINE('> Execution time of Normal View');
   v_start_time := '--> Start Time: '||To_Char(SYSDATE,'DD-MM-YYYY @ HH:MI:SS AM');
   DBMS_OUTPUT.PUT_LINE(v_start_time);
   FOR a IN c1 
   LOOP
       v_dummy := 1;
   END LOOP;
   v_end_time := '> End Time  : '||To_Char(SYSDATE,'DD-MM-YYYY @ HH:MI:SS AM');
   DBMS_OUTPUT.PUT_LINE(v_end_time);
   DBMS_OUTPUT.PUT_LINE('>');
   DBMS_OUTPUT.PUT_LINE('> Execution time of Materialized View');
   v_start_time := '> Start Time: '||To_Char(SYSDATE,'DD-MM-YYYY @ HH:MI:SS AM');
   DBMS_OUTPUT.PUT_LINE(v_start_time);
   DBMS_OUTPUT.PUT_LINE('>');
   FOR b IN c2 
   LOOP
       v_dummy := 1;
   END LOOP;
   v_end_time := '> End Time  : '||To_Char(SYSDATE,'DD-MM-YYYY @ HH:MI:SS AM');
   DBMS_OUTPUT.PUT_LINE(v_end_time);
   DBMS_OUTPUT.PUT_LINE('> You can notice the time in execution of both the Views');
END;
/

PROMPT-->
PROMPT-- Added on 15/10/2007
PROMPT-- Now we will test the performance of Materialized Views 
PROMPT-- by using Execution Plan.
PROMPT-- Check the value of "Card" and "Bytes" in Execution Plan.
PROMPT-- Check the value of "consistent gets" and "physical reads" 
PROMPT-- in Statistices.
PROMPT-- You will notice that permormance is improved drastically.
PROMPT-->

SET AUTOTRACE ON
SET TIMING ON

PROMPT--> Selecting records from Normal View

select mv_col2,count(*) from x3_bhs_normal_view group by mv_col2
/

PROMPT-->
PROMPT-- Notice that we may have created a materialized view, but when we ANALYZE, 
PROMPT-- we are analyzing a TABLE. "A materialized view creates a real table", and 
PROMPT-- this table may be indexed, analyzed, and so on. 
PROMPT-->

ANALYZE TABLE x3_bhs_mv_view compute statistics
/
PROMPT--> Selecting records from Materialized View

select mv_col2,count(*) from x3_bhs_mv_view group by mv_col2
/
PROMPT --> Dropping ojects created for Materialized View test

DROP SEQUENCE x3_bhs_mv_seq
/
DROP TABLE x3_bhs_mv_big_tab
/
DROP MATERIALIZED VIEW x3_bhs_mv_view
/
DROP VIEW x3_bhs_normal_view
/
PROMPT --> Ojects dropped which were created to test performance of Materialized Views

SET AUTOTRACE OFF
SET TIMING OFF
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON
SET PAGESIZE 500
SET LINESIZE 100
SET TRIMSPOOL OFF

SPOOL OFF

PROMPT--> Log File generated is : &file_path2