SQL

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 oracle user.
SQL> CONNECT oracle/learning
 

To do this Hands-On exercise, you must first create a materialized view log.
 

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)

You can not perform DML on snapshots in this category.

You can insert, update and delete rows of the updatable materialized view.

Example:

create materialized view MVIEW_test

Materialized views that are created with sub-queries in the WHERE clause of the mview query are referred to as subquery materialized views.

Example:

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);

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:

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.
 

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 the CREATE MATERIALIZED VIEW privilege to the oracle user.
SQL> GRANT CREATE MATERIALIZED VIEW TO oracle
SQL> /
 

Now, log back in as the oracle user.
SQL> CONNECT oracle/learning
 

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.
 

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.
 

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.
 

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.
 

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);
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> /
 

Log back in as system/manager and revoke the CREATE MATERIALIZED VIEW privilege from the ORACLE user.
 

SQL> CONNECT system/manager
SQL> REVOKE CREATE MATERIALIZED VIEW FROM oracle
SQL> /
you have now cleared your session, so that you can perform this excercise over and over again.
 

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?