This practice will familiarize you with the various features and privileges to ensure successful creation of a materialized view from a base table.

1) Grant the necessary privileges for user Scott to create materialized views and allow query rewrite on the materialized views owned by schema Scott.
 

As user SYSTEM, execute the following command:   > grant CREATE MATERIALIZED VIEW, QUERY REWRITE to scott;

2) As user Scott, create a materialized view name STAFF_MV_SIMPLE from the Employees table. You want the materialized view to only store data  for the job of a STAFF, and you want a complete refresh.  You need to first create the EMPLOYEES table by importing employees.dmp.
 

As user Scott, execute the following command:  > CREATE MATERIALIZED VIEW staff_mv_simple    REFRESH COMPLETE    AS SELECT * FROM EMPLOYEES  WHERE JOB = ‘STAFF’;

3) Create a materialized view name STAFF_MV_REFRESH, still only storing data  for the job of a STAFF,  but you want a refresh feature that will only apply the changes made to the base table since the last time you refresh the materialized view.  You will be creating a materialized view with a fast refresh.
 

As user Scott, execute the following command:  > CREATE MATERIALIZED VIEW staff_mv_refresh    REFRESH FAST    AS SELECT * FROM EMPLOYEES  WHERE JOB = ‘STAFF’;

4) Create a materialized view name STAFF_MV_QR, still only storing data  for the job of a STAFF, using 2 parallel processes, allowing query rewrite, and you
want a complete refresh.
 

As user Scott, execute the following command:  > CREATE MATERIALIZED VIEW staff_mv_qr     PARALLEL (DEGREE 2)     REFRESH COMPLETE     ENABLE QUERY REWRITE     AS SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’;

This practice will familiarize you with the various features of creating a materialized view with query rewrite capabilty.

1) Alter your session to allow query rewrite.
 

As user Scott, execute the following command:  > alter session set QUERY_REWRITE_ENABLED = true;

2) Use EXPLAIN PLAN to verify that rewrite has taken place. Confirm you have a Plan_Table.  If you do not, please create it by running the utlxplan.sql file.
It should be located in the subdirectory where you installed Oracle.

For example, if Oracle 8.1.6 is install on c:\oracle, then the file will be in the c:\oracle\ora81\rdbms\admin

Create the Plan_table for schema Scott if it does not exist already.
 

As user Scott, execute the following command:  > @c:\oracle\ora81\rdbms\admin\utlxplan.sql

 Confirm the plan_table exists.
 

As user Scott, execute the following command:  > describe plan_table

3) Confirm materialized view STAFF_MV_QR will be use in a query rewrite request.
 

As user Scott, execute the following command:  >delete from plan_table; This is to ensure there are no row the the plan_table before populaing it with the explain plan results.  >explain plan for  > SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’  >col Operation format a30   col Options   format a20   col Object    format a20  >select lpad(' ', 2*LEVEL) || OPERATION ||             decode( ID, 0, ' Cost = '||POSITION) "Operation",             OPTIONS "Options", OBJECT_NAME "Object"   from PLAN_TABLE            connect by prior ID = PARENT_ID  start with ID = 0            order by ID   /

 

Practice: Module 1 - Topic 3: Dimensions

This practice will familiarize you with the various features of creating a dimension, storing the hierachy definition in the database, and being familiar with the the data dictionary views that can be used to gather information regarding dimensions.

1) Confirm user Scott has the privilege to create a dimension.  If not,  grant that privilege to Scott.
 

As user System, execute the following command:  > select grantee, privilege    from dba_sys_privs    where grantee = 'SCOTT';  If you don't see user Scott has the CREATE DIMENSION privilege, grant it to user Scott.  > grant create dimension to scott;

2) As user Scott, create a dimension name mv_time_dim from the time table with a hierarchy name scott_calendar.  Frist create the time table by exporting from
the file time.dmp.
 

As user Scott execute the following command:  >CREATE DIMENSION mv_time_dim   LEVEL sdate IS time.sdate   LEVEL month IS time.month   LEVEL qtr   IS time.quarter   LEVEL yr    IS time.year   HIERARCHY scott_calendar   (sdate CHILD OF month CHILD OF qtr CHILD OF  yr)   ATTRIBUTE month DETERMINES month_name;

3) Determine the levels of the dimersion you have created.  To see that information, query the user_dim_levels view.
 

As user Scott execute the following command:  >select dimension_name, level_name, detailobj_name   from user_dim_levels;

1) After you have set up Oracle Trace Manager to monitor the utilization of your materialized views. you can determine if you should keep the materialized
views you have created by querying the mview$_recommendations view.
 

As user Scott execute the following command:  >SELECT recommended_action, mview_name, group_by_columns, measures_list   FROM mview$_recommendations;