|
Your organization is
growing, and their managers from different states
are in need of data warehousing. You should
pre-compute database information into a summary data
aggregated to be easily understood.
They are asking you to
create a materialized view to pre-compute and store
information manually on the number of employees with
their total salary paid for each department (to
project the employees� payment budget). They want,
when new employees and their salaries are entered
into the base tables, to be able to manually update
their materialized view with its summary, aggregated
data, to reflect these changes from the base tables.
They should be able to
download a subset of data from the company�s
headquarter table to another company associated
servers, assuming that their system is not in a
distributed environment.
In a distributed
environment, they could use materialized views to
replicate data at distributed sites and synchronize
updates between these sites.
Now, the company wants
the ability to create an aggregated snapshot for the
accounting department only. The required data
include the total number of employees, and the total
paid salaries for the accounting department only.
Once the aggregated snapshot was created, it should
define, in such a way, that it can update the
materialized view once a month without any human
intervention. The snapshot must be refreshed, based
on the rule that your client asked.
-- Hands-On 01
(Materialized Views - The view that stores data)
SET ECHO ON
CLEAR SCR
-- Log in to sqlplus as the oracle user.
--
Pause
CONNECT oracle/learning
pause
CLEAR SCR
-- To do this Hands-On exercise, you must first
create
-- a materialized view log.
-- Create a materialized view log with a PRIMARY
KEY,
-- the ROWID, and <INCLUDING NEW VALUES> options.
--
pause
CREATE MATERIALIZED VIEW LOG ON emp
WITH PRIMARY KEY,
ROWID (deptno)
INCLUDING NEW VALUES
/
pause
CLEAR SCR
-- Begin by setting your linesize to 1000.
--
pause
SET LINESIZE 1000;
pause
CLEAR SCR
-- Query your snapshot logs' view.
--
Pause
SELECT * FROM user_snapshot_logs
/
-- This result shows that your log was
-- created successfully and named MLOG$_EMP.
--
pause
CLEAR SCR
-- Create a materialized view with the BUILD
IMMEDIATE
-- and REFRESH ON DEMAND options.
-- Your materialized view should have the department
number,
-- number of employees, and total salaries paid to
employees
-- by department.
--
pause
CREATE MATERIALIZED VIEW mv_sal
BUILD IMMEDIATE
REFRESH ON DEMAND
AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS
salary
FROM emp
GROUP BY deptno
/
-- Notice on the error message INSUFFICIENT
PRIVILEGE.
-- In order to create a materialized view, you must
be granted a
-- CREATE MATERIALIZED VIEW privilege.
--
Pause
CLEAR SCR
-- Connect to SQLPLUS as system/manager.
pause
CONNECT system/manager
pause
CLEAR SCR
-- Grant the CREATE MATERIALIZED VIEW privilege
-- to the oracle user.
--
pause
GRANT CREATE MATERIALIZED VIEW TO oracle
/
pause
CLEAR SCR
-- Now, log back in as the oracle user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- This time you should be able to create a
-- materialized view without any problems.
-- Create your materialized view again.
--
pause
CREATE MATERIALIZED VIEW mv_sal
BUILD IMMEDIATE
REFRESH ON DEMAND
AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS
salary
FROM emp
GROUP BY deptno
/
-- Note that this time the materialized view was
-- created successfully.
--
Pause
CLEAR SCR
-- Query the user's materialized views.
--
pause
SELECT * FROM user_mviews
/
pause
CLEAR SCR
-- Query the mv_sal materialized view.
--
pause
SELECT * FROM mv_sal
/
-- Note the total salary paid to department 10.
pause
CLEAR SCR
-- Query all the information about empno 7934.
--
pause
SELECT * FROM emp WHERE empno = 7934
/
-- Make a note about her salary.
--
pause
CLEAR SCR
-- Change her salary to 5000 and save it
-- into the EMP table.
--
Pause
UPDATE emp
SET sal = 5000
WHERE empno = 7934
/
COMMIT
/
-- The salary column was changed.
--
pause
CLEAR SCR
-- Query the mv_sal materialized view.
--
Pause
SELECT * FROM mv_sal
/
-- Notice that there are no changes made on
-- the total salary in department 10.
--
Pause
CLEAR SCR
-- Refresh the mv_sal materialized view by
-- executing the REFRESH procedure at the
-- DBMS_SNAPSHOT package and use the letter C
-- for the complete option.
--
pause
EXECUTE dbms_snapshot.refresh('mv_sal','C')
/
pause
CLEAR SCR
-- Now, query your mv_sal materialized view.
--
pause
SELECT * FROM mv_sal
/
-- Notice that the total salary paid to department
10
-- was changed. In fact, you have manually updated
-- the materialized view.
pause
CLEAR SCR
-- Now, let's create a materialized view named
mv_account
-- that would be updated every month automatically
without
-- any human intervention.
--
pause
CREATE MATERIALIZED VIEW mv_account
BUILD IMMEDIATE
REFRESH FORCE
START WITH SYSDATE NEXT (SYSDATE+30)
WITH PRIMARY KEY
AS
SELECT * FROM emp
WHERE deptno = 10
/
-- Notice that the START WITH SYSDATE option will
create
-- an immediate data, and the NEXT (SYSDATE+30)
option
-- will update the table every 30 days.
--
pause
CLEAR SCR
-- Check to see how many materialized views you have
created.
--
pause
SELECT * FROM user_mviews
/
-- You got one more materialized view.
pause
CLEAR SCR
-- Query the mv_account materialized view.
--
pause
SELECT * FROM mv_account
/
pause
CLEAR SCR
-- Insert a record to your EMP table.
--
pause
INSERT INTO emp VALUES
(9999,'John','Kazerooni',7782,'04-Apr-02',1400,500,10)
/
COMMIT
/
-- A record was added.
--
pause
CLEAR SCR
-- Since you have 30 days to see your changes,
-- you can update the mv_account materialized view
by
-- executing the REFRESH procedure from the
-- DBMS_SNAPSHOT package and use the C option for
-- the complete option.
--
pause
EXECUTE dbms_snapshot.refresh('mv_account','C')
/
pause
CLEAR SCR
-- Now, query the mv_account table.
--
pause
SELECT * FROM mv_account
/
-- Notice that employee number 9999 was added
-- to the materialized view.
--
pause
CLEAR SCR
-- Check your jobs' queue.
--
pause
SELECT * FROM user_jobs
/
-- Notice that this job is going to run each month.
-- Make a note of it's job number.
--
pause
CLEAR SCR
-- Execute the REMOVE procedure from the DBMS_JOB
package
-- to delete a job from the job's queue.
-- Use the runtime variable to enter your job
number.
--
pause
EXECUTE dbms_job.remove(&Enter_job_number)
/
pause
CLEAR SCR
-- Delete the added employee number 8888 and 9999
-- and change the employee's salary back to 1000.
--
pause
DELETE FROM emp WHERE empno IN (8888,9999);
UPDATE emp
SET sal = 1000
WHERE empno = 7934
/
COMMIT
/
pause
CLEAR SCR
-- Drop all of the created Materialized views.
--
pause
DROP MATERIALIZED VIEW LOG ON emp
/
DROP MATERIALIZED VIEW mv_sal
/
DROP MATERIALIZED VIEW mv_account
/
pause
CLEAR SCR
-- Log back in as system/manager and revoke
-- the CREATE MATERIALIZED VIEW privilege
-- from the ORACLE user.
--
pause
CONNECT system/manager
REVOKE CREATE MATERIALIZED VIEW FROM oracle
/
-- You have now cleared your session, so that you
can
-- perform this exercise over and over again.
pause
CONNECT oracle/learning
CLEAR SCR
-- Now, You should practice this Hands-On over and
over
-- until you become a master at it.
-- For more information about the subject, you are
encouraged
-- to read from a wide selection of available books.
-- Good luck.
--
pause |
