.2
CSCI315 Database Design and Implementation
Experiment 12.2
How to create materialized views ?
Experimented and written by Dr. Janusz R. Getta
School of Computer Science and Software Engineering, University of Wollongong, Australia,
Bldg. 3, room 210, phone +61 02 42214339, fax +61 02 42214170,
e-mail: [email protected], Web: http://www.uow.edu.au/∼jrg, Msn: jgetta, Skype: jgetta007
Table of contents
A0 How to begin and what you need to know before you start ?
A1 How to grant CREATE MATERIALIZED VIEW privilege ?
A2 How to create a materialized view log ?
A3 How to create a materialized view ?
A4 How to test a materialized view ?
A5 How to update a master table ?
A6 How to test a materialized view ?
A7 How to create a materialized view and log ?
A8 How to test a materialized view ?
A9 How to drop the materialized views and logs ?
References
Actions
A0 How to begin and what you need to know before you start ?
0
These are the specifications of homeworks in a subject Database Design and Implementation (CSCI315)
taught in Spring session 2011 at School of Computer Science and Software Engineering, University of Wollongong by Dr. Janusz R. Getta
12-1
Experiment 12.2: How to create materialized views ?
12-2
A printable copy of this experiment in pdf format is available here .Turn the system on (if
it is off) and logon as PC user. Download and uncompress SQL scripts in tar.gz format or
SQL scripts in zip format used in this experiment. Connect as a user SYSTEM to one of
data-pc.. database servers and and execute a script listpar.sql to list the present value of a
system initalization parameter optimizer_mode .An expected value of system initialization
parameter optimizer_mode should be ALL_ROWS .If a value of system initialization parameter
otimizer_mode is not equal to ALL_ROWS then while connected as a user SYSTEM execute the
following statement:
ALTER SYSTEM SET OPTIMIZER_MODE=ALL_ROWS;
and once more verify a value of the parameter optimizer_mode .
A1 How to grant CREATE MATERIALIZED VIEW privilege ?
To be able to create a materialized view you have to grant CREATE MATERIALIZED VIEW privilege to the ordinary user. Connect as a user SYSTEM and execute the following statement.
GRANT CREATE MATERIALIZED VIEW TO CSCI315;
Oracle uses materialized view log , to propagate the modifications of master tables to materialized views. When DML changes are made to the master tables data, Oracle stores rows
describing those changes in the materialized view log and then uses the materialized view log
to refresh materialized views based on the master table. This process is called an incremental
or fast refresh . Without a materialized view log, Oracle must re-execute the materialized
view query to refresh the materialized view. This process is called a complete refresh . A
materialized view log is located in the master database in the same schema as the master
table. A master table can have only one materialized view log defined on it. Oracle can use
this materialized view log to perform fast refreshes for all fast-refreshable materialized views
based on the master table.
A2 How to create a materialized view log ?
This action creates a materialized view log. Connect as a user CSCI315 and execute the
following statement.
CREATE MATERIALIZED VIEW LOG ON ORDERS;
We have just created a materialized view log on table ORDERS . It means that each time,
table ORDERS is modified the system automatically refreshes the materialized views built on
the table.
A3 How to create a materialized view ?
Experiment 12.2: How to create materialized views ?
12-3
Finally, we create a materialized view. While connected as a user CSCI315 execute the
following statement.
CREATE MATERIALIZED VIEW MV_ORDERS
REFRESH ON COMMIT
AS(
SELECT O_ORDERKEY,
O_CUSTKEY,
O_TOTALPRICE,
O_ORDERDATE
FROM ORDERS
WHERE O_ORDERDATE > TO_DATE(’31-DEC-1986’,’DD-MON-YYYY’) );
A clause REFRESH ON COMMIT means that materialized view is automatically refreshed each
time the modifications made to master table ORDERS are committed.
A4 How to test a materialized view ?
Next, while connected as a user CSCI315 execute the statements:
SELECT *
FROM MV_ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
SELECT O_ORDERKEY,
O_CUSTKEY,
O_TOTALPRICE,
O_ORDERDATE
FROM ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
The statements above produce exactly the same results.
A5 How to update a master table ?
Next, while connected as CSCI315 update the mater table ORDERS and repeat the statements
listed above.
UPDATE ORDERS
SET O_TOTALPRICE = 0
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
Experiment 12.2: How to create materialized views ?
12-4
SELECT *
FROM MV_ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
SELECT O_ORDERKEY,
O_CUSTKEY,
O_TOTALPRICE,
O_ORDERDATE
FROM ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
Now, the statements above produce different results. This is the major difference between
relational views and materialized views. Materialized views occupy persistent storage and
are refreshed accordingly to a predefined strategy. In this particular case a view MV_ORDERS is
refreshed after updates are committed.
A6 How to test a materialized view ?
While connected as a user CSCI315 commit the updates performed on ORDERS table and list
the contents of materialized view once more.
COMMIT;
SELECT *
FROM MV_ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
SELECT O_ORDERKEY,
O_CUSTKEY,
Experiment 12.2: How to create materialized views ?
12-5
O_TOTALPRICE,
O_ORDERDATE
FROM ORDERS
WHERE O_ORDERDATE = TO_DATE(’01-JAN-1992’,’DD-MON-YYYY’);
Now, the statements above produce the same results.
A7 How to create a materialized view and log ?
While connected as a user CSCI315 create another materialized view automatically refreshed
by the system every minute. Execute the following statements.
CREATE MATERIALIZED VIEW LOG ON PART;
CREATE MATERIALIZED VIEW MV_PART
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/(24*60)
AS
SELECT *
FROM PART;
A8 How to test a materialized view ?
While connected as a suer CSCI315 , execute UPDATE statement given below and access a view
MV_PART immediately after UPDATE . Repeat SELECT on MV_PART until the view is updated.
UPDATE PART
SET P_SIZE = 100
WHERE P_PARTKEY = 1;
SELECT *
FROM PART
WHERE P_PARTKEY = 1;
SELECT *
FROM MV_PART
WHERE P_PARTKEY = 1;
Experiment 12.2: How to create materialized views ?
12-6
A9 How to drop the materialized views and logs ?
To drop the logs and materialized views connect as a user CSCI315 and execute the following
statements.
DROP
DROP
DROP
DROP
MATERIALIZED
MATERIALIZED
MATERIALIZED
MATERIALIZED
VIEW
VIEW
VIEW
VIEW
MV_ORDERS;
LOG ON ORDERS;
MV_PART;
LOG ON PART;
References
Oracle9i, SQL Reference, SELECT statement
Oracle9i, SQL Reference, CREATE TABLESPACE statement
Oracle9i, SQL Reference, DROP TABLESPACE statement
Oracle9i, SQL Reference, ALTER USER statement
Oracle9i, SQL Reference, EXPLAIN PLAN statement
Oracle9i, SQL Reference, DROP TABLE statement
Oracle9i, Reference, DBA TABLES view
Oracle9i, Reference, DBA TAB COLUMNS view
Oracle9i, Reference, DBA TAB HISTOGRAMS view
Oracle9i, Reference, PLAN TABLE table