"I find that the
harder I work, the more luck I seem to have." - Thomas
Jefferson (1743-1826) |
Read
first then play the video:
SQL-VIDEO -Materialized
views - the view that stores data
Materialized Views - The
view that stores data
Introduction
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.
Your assignments are:
1- To create a materialized
view log,
2- To create a materialized
view,
3- To test your
materialized view,
4- To execute or update you
materialized view manually,
5- To create materialized
view and updated monthly,
6- To test it, and
7- To check and test your
monthly job.
Topics:
-
Creating a materialized
view log
-
Listing the snapshot logs'
view
-
MLOG$_EMP
-
Creating a materialized
view
-
Granting materialized view
privilege
-
Listing the user's
materialized views
-
Executing the REFRESH
procedure
-
Using DBMS_SNAPSHOT package
-
Executing the REMOVE
procedure
-
Listing the materialized
view in the job"s queue
-
Deleting the materialized
view"s job
-
Dropping the created
Materialized view
-
Revoking the materialized
view privilege
Log in to sqlplus as the
iself user.
SQL> CONNECT iself/schooling
To do this Hands-On
exercise, you must first create a materialized view log.
CREATE MATERIALIZED VIEW
...
PRIMARY KEY, ROWID, and
INCLUDING NEW VALUES
A materialized view (MVIEW)
is a replica of a target master from a single point in time. The
concept was first introduced with Oracle7 termed as SNAPSHOT. You use
Materialized Views to create summaries in a data warehouse environment
or replicate a data in a distributed environment. In data warehouses,
you can use materialized views to pre-compute and store aggregated
data such as the sum of sales. In distributed environments, you can
use materialized views to replicate data from a master site to other
distributed sites.
Remember that the following
datatypes in the Oracle database are not supported in snapshot
replication:
- LONG
- LONG RAW
- BFILE
- UROWID (cot supported for
updatable snapshots)
Materialized View Types
Read-Only Materialized
Views
You can not perform DML on
snapshots in this category.
Updatable Materialized
Views
You can insert, update and
delete rows of the updatable materialized view.
Example:
SQL> create
materialized view MVIEW_test
refresh fast
start with sysdate
next sysdate+1
FOR UPDATE as
select * from mytable@california;
Subquery Materialized Views
Materialized views that are
created with sub-queries in the WHERE clause of the mview query are
referred to as subquery materialized views.
Example:
SQL> CREATE MATERIALIZED VIEW
myorders
REFRESH FAST AS
SELECT * FROM myorder@california
o
WHERE EXISTS
(SELECT * FROM mycustomer@dc
c
WHERE c.customer_id =
o.customer_id
AND c.credit_limit >
10000);
Rowid vs. Primary Key
Materialized Views
Fast refresh requires
association between rows at snapshot and master sites. Snapshots that
use ROWIDs to refresh are called ROWID snapshots while those that use
primary keys are called primary key snapshots.
Example:
SQL> CREATE MATERIALIZED VIEW
orders
REFRESH WITH ROWID AS
SELECT * FROM
orders@califonia;
Create a materialized view
log with a PRIMARY KEY, the ROWID, and INCLUDING NEW VALUES options.
SQL> CREATE MATERIALIZED VIEW LOG ON emp
WITH PRIMARY KEY,
ROWID (deptno)
INCLUDING NEW VALUES
SQL> /
Begin by setting your
linesize to 1000.
SQL> SET LINESIZE 1000;
Query your snapshot logs'
view.
SQL> SELECT * FROM user_snapshot_logs
SQL> /
This result shows that your log was created successfully
and named MLOG$_EMP.
BUILD IMMEDIATE and REFRESH
ON DEMAND
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.
SQL> 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
SQL> /
Notice on the error message INSUFFICIENT PRIVILEGE.
In order to create a materialized view, you must be granted a CREATE
MATERIALIZED VIEW privilege.
Connect to SQLPLUS as
system/manager.
SQL> CONNECT system/manager
GRANT CREATE MATERIALZED
VIEW "
Grant the CREATE
MATERIALIZED VIEW privilege to the iself user.
SQL> GRANT CREATE MATERIALIZED VIEW TO
iself
SQL> /
Now, log back in as the
iself user.
SQL> CONNECT iself/schooling
This time you should be
able to create a materialized view without any problems.
Create your materialized view again.
SQL> 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
SQL> /
Note that this time the materialized view was created
successfully.
Query the user's
materialized views.
SQL> SELECT * FROM user_mviews
SQL> /
Query the mv_sal
materialized view.
SQL> SELECT * FROM mv_sal
SQL> /
Note the total salary paid to department 10.
Query all the information
about empno 7934.
SQL> SELECT * FROM emp WHERE empno = 7934
SQL> /
Make a note about her salary.
Change her salary to 5000
and save it into the EMP table.
SQL> UPDATE emp
SET sal = 5000
WHERE empno = 7934
SQL> /
SQL> COMMIT
SQL> /
The salary column was changed.
Query the mv_sal
materialized view.
SQL> SELECT * FROM mv_sal
SQL> /
Notice that there are no changes made on the total salary
in department 10.
DBMS_SNAPSHOT package and
REFRESH option
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.
SQL> EXECUTE
dbms_snapshot.refresh('mv_sal','C')
SQL> /
Now, query your mv_sal
materialized view.
SQL> SELECT * FROM mv_sal
SQL> /
Notice that the total salary paid to department 10 was
changed. In fact, you have manually updated the materialized view.
START WITH SYSDATE NEXT "
Now, let's create a
materialized view named mv_account that would be updated every month
automatically without any human intervention.
SQL> 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
SQL> /
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.
Check to see how many
materialized views you have created.
SQL> SELECT * FROM user_mviews
SQL> /
you got one more materialized view.
Query the mv_account
materialized view.
SQL> SELECT * FROM mv_account
SQL> /
Insert a record to your EMP
table.
SQL> INSERT INTO emp VALUES
(9999,'John','Kazerooni',7782,'04-Apr-02',1400,500,10)
SQL> /
SQL> COMMIT
SQL> /
A record was added.
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.
SQL> EXECUTE
dbms_snapshot.refresh('mv_account','C')
SQL> /
Now, query the mv_account
table.
SQL> SELECT * FROM mv_account
SQL> /
Notice that employee number 9999 was added to the materialized view.
USER_JOBS table
Check your jobs' queue.
SQL> SELECT * FROM user_jobs
SQL> /
Notice that this job is going to run each month. Make a
note of it's job number.
DBMS_JOB package and REMOVE
procedure
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.
SQL> EXECUTE dbms_job.remove(&Enter_job_number)
SQL> /
Delete the added employee
number 8888 and 9999 and change the employee's salary back to 1000.
SQL> DELETE FROM emp WHERE empno IN
(8888,9999);
SQL>
UPDATE emp
SET sal = 1000
WHERE empno = 7934
SQL> /
SQL> COMMIT
SQL> /
Drop all of the created
Materialized views.
SQL> DROP MATERIALIZED VIEW LOG ON emp
SQL> /
SQL> DROP MATERIALIZED VIEW mv_sal
SQL> /
SQL> DROP MATERIALIZED VIEW mv_account
SQL> /
REVOKE CREATE MATERIALIZED
VIEW "
Log back in as
system/manager and revoke the CREATE MATERIALIZED VIEW privilege from
the ISELF user.
SQL> CONNECT
system/manager
SQL> REVOKE CREATE MATERIALIZED VIEW FROM iself
SQL> /
you have now cleared your session, so that you can perform
this excercise over and over again.
"Each problem
that I solved became a rule which served afterwards to solve
other problems." - Rene Descartes (1596-1650), "Discours
de la Methode" |
Questions:
Q: What is a Materialized
View?
Q: What are the
Materialized View types?
Q: Write the difference
between ROWID and PRIMARY KEY in the Materialized View.
Q: What is the difference
between a Materialized View and View?
Q: When or why do you use a
Materialized View?
Q: What is a materialized
view log?
Q: What are the PRIMARY KEY
and ROWID in the Materialized View Log?
Q: What does the
USER_SNAPSHOT_LOGS view contain?
Q: Create a materialized
view that contains the department number, number of employees, and
total salaries paid to employees by department.
Q: Who can create a
materialized view?
Q: What does the
USER_MVIEWS view contain?
Q: How do you refresh a
materialized view?
Q: What parameter should be
used to update the materialized view every month automatically without
human intervention?
Q: What does the USER_JOBS
view contain?
Q: How do you remove a job
from the Oracle Job Queue?
Q: How do you drop a
materialized view log and a materialized view?
|