MATERIALIZED VIEW : THE SECRET TO FASTER QUERY PERFORMANCE
A Materialized view is a database object that stores the result of a query in a physical table, making it possible to quickly retrieve the data without having to execute the query again. This technique is used to improve the performance of complex queries, reduce the load on the database, and enhance the overall efficiency of data analysis and reporting.
WHAT IS MATERIALIZED VIEW
- ORACLE 8I INTRODUCED MATERIALIZED VIEWS. GENERALLY, VIEWS DOESN’T STORE ANY DATA WHERE AS MATERIALIZED VIEWS SQL STORING DATA.
- THESE VIEWS ARE USED IN DATA WAREHOUSING AND HANDLING BY DBA. MATERIALIZED VIEWS ALSO CREATED FROM BASE TABLES.
DIFFERENCES BETWEEN VIEW AND MATERIALIZED VIEW:
VIEW
- VIEW DOES NOT STORE ANY DATA.
- WHEN WE DROPPING BASE TABLE THEN VIEW CANNOT BE ACCESSIBLE.
- IT IS DEPENDENT OBJECT
- WE CAN PERFORM DML OPERATIONS ON VIEW.
MATERIALIZED VIEW
- MATERIALIZED VIEW STORE
DATA. - WHEN WE DROPPING BASE
TABLE THEN MATERIALIZED
VIEW CAN BE ACCESSIBLE. - IT IS INDEPENDENT OBJECT.
- WE CANNOT PERFORM DML
OPERATIONS ON MATERIALIZED VIEW.
SYNTAX: CREATE MATERIALIZED VIEW <VIEW NAME> AS SELECT * FROM
<TABLE NAME>;
EX: SQL> CREATE TABLE TEST1 (SNO INT, NAME VARCHAR2 (20));
SQL> CREATE VIEW V1 AS SELECT * FROM TEST1;
VIEW CREATED.
SQL> CREATE MATERIALIZED VIEW MV1 AS SELECT * FROM TEST1;
SQL MATERIALIZED VIEW CREATED.
- TESTING:
SQL> INSERT INTO TEST1 VALUES (101,’SMITH’); - HERE, BASE TABLE (TEST1) AND VIEW (V1) TABLE DATA IS UPDATED BUT MATERIALIZED VIEW (MV1) TABLE DATA IS NOT UPDATED. IF WE WANT TO UPDATE DATA IN MATERIALIZED VIEW THEN WE REFRESH MATERIALIZED VIEW BY USING THE FOLLOWING SYNTAX,
SYNTAX:
EXEC DBMS_MVIEW.REFRESH (‘MATERIALIZED VIEW NAME’);
EX: EXEC DBMS_MVIEW.REFRESH (‘MV1’);
ON DEMAND / ON COMMIT:
IN ORACLE WE ARE REFRESHING MATERIALIZED VIEW IN TWO WAYS THOSE ARE,
I) ON DEMAND:
IT IS A DEFAULT REFERESHING METHOD.IN THIS METHOD WE ARE REFRESHING MATERIALIZED VIEW BY USING “DBMS_MVIEW” PROCEDURE.
SYNTAX: EXECUTE DBMS_MVIEW.REFRESH (‘MVIEW NAME’);
EX: EXECUTE DBMS_MVIEW.REFRESH (‘MV1’);
II) ON COMMIT:
WE CAN REFRESHING A MATERIALIZED VIEW WITHOUT USING “DBMS_MVIEW” BUT USING “ON COMMIT” METHOD.
SYNTAX: CREATE MATERIALIZED VIEW <VIEW NAME>
REFRESH ON COMMIT
AS SELECT * FROM <TN>;
EX:
CREATE TABLE TEST2 (EID INT, SAL NUMBER (10));
EX:
CREATE MATERIALIZED VIEW MV2 REFRESH ON COMMIT
AS SELECT * FROM TEST2;
ERROR:
TABLE ‘TEST2’ DOES NOT CONTAIN A PRIMARY KEY CONSTRAINT.
EX:
CREATE TABLE TEST2 (EID INT PRIMARY KEY, SAL NUMBER (10));
EX:
CREATE MATERIALIZED VIEW MV2 REFRESH ON COMMIT
AS SELECT * FROM TEST2;
MATERIALIZED VIEW CREATED.
NOTE: WHEN WE CREATE MATERIALIZED VIEW ALONG WITH REFRESH ON COMMIT METHOD ON BASE TABLE THEN BASE TABLE SHOULD HAVE PRIMARY KEY CONSTRAINT OTHERWISE ORACLE RETURNS AN ERROR.
TESTING:
SQL> INSERT INTO TEST2 VALUES (101,25000);
SQL> COMMIT;
SQL> SELECT * FROM MV2;
NOTE: IF WE WANT TO VIEW MATERIALIZED VIEWS
THEN WE ARE USING THE FOLLOWING DATA DICTIONARY IS
“USER_MVIEWS”.
EX:
SQL> DESC USER_MVIEWS;
SQL> SELECT MVIEW_NAME FROM USER_MVIEWS;
SYNTAX TO DROP MATERIALIZED VIEW:
SQL> DROP MATERIALIZED VIEW <MVIEW NAME>;
SQL> DROP MATERIALIZED VIEW MV1;
MATERIALIZED VIEW DROPPED.
