Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be explaining the concept of Materialized Views in Oracle, also known as Snapshots.
It is a structure that stores the data returned by a query in a table and updates this data with the main table at certain intervals.
The user who will create Mview must have “CREATE MATERIALIZED VIEW” and “CREATE TABLE” system permissions.
Some important terms related to Materialized Views.
Materialized Views: It is a database object that replicates data and improves performance.
Master Table: The table whose data is queried.
Complete Refresh: It is the deletion of the MView table and complete renewal of the main table.
Fast Refresh: Only DML changes in the main table are applied to Mview.
Refresh Method: It determines the renewal method. It can be Complete or Fast.
Materialized View Log: It is created when Fast Refresh is used. It is the database object that tracks DML changes in the main table.
Build Mode: It determines the Mview mode. It can be Immediate or Deferred.
Refresh Mode: It determines the renewal mode. It can be Demand, on Commit or Never.
Query Rewrite: It allows the Optimizer to consider the Mview SQL work plan instead of the main table.
Refresh Group: It is the group where the MVs to be refreshed at the same time are defined.
“Complete Refresh” Materialized Views that will completely retrieve the data from the main table each time are created as follows.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE MATERIALIZED VIEW ACCOUNT_MV SEGMENT CREATION IMMEDIATE NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT ACCOUNT_ID, ACCOUNTNAME, STATUS FROM IPTVMWC.ACCOUNT; |
If the view is queried after it is created, it will return results immediately. Because we made the BUILD parameter in the view IMMEDIATE.
1 2 3 4 5 | SQL> SELECT COUNT(*) FROM ACOUNT_MV; COUNT(*) ---------- 1193405 |
Even if we perform insert operations as below after the view is created, the result will be the same since we do not refresh it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> SELECT COUNT(*) FROM ACCOUNT_MV; COUNT(*) ---------- 1193402 SQL> INSERT INTO IPTVMWC.ACCOUNT VALUES ('9999997', '1', '939009997', 'aXyZ', '', '1', ''); 1 row created. SQL> commit; Commit complete. SQL> SQL> SQL> SQL> SELECT COUNT(*) FROM ACCOUNT_MV; COUNT(*) ---------- 1193402 |
Mviews are renewed with the DBMS_MVIEW package as follows.
The DBMS_MVIEW package has 2 parameters. The first is the MV name and the other is the Mview renewal method. The value “C” is used for Complete renewal and “F” is used for Fast renewal.
1 2 3 4 5 6 7 8 9 10 11 | SQL> EXEC DBMS_MVIEW.REFRESH ('ACCOUNT_MV', 'C'); PL/SQL procedure successfully completed. View yenilendikten sonra satır sayısı sorgulandığında sayının değiştiği görülür. SQL> SELECT COUNT(*) FROM ACCOUNT_MV; COUNT(*) ---------- 1193405 |
Fast Refresh Mview is created as follows.
1. Mview Log is created. Mview Log is a database object that tracks DML changes in the main table. In order to create an Mview Log, there must be a primary key in our main table.
1 2 3 4 5 6 | SQL> CREATE MATERIALIZED VIEW LOG ON ACCOUNT WITH PRIMARY KEY; Ana tablomuzda primary key yoksa ve aşağıdaki gibi hata alınır. ERROR at line 1: ORA-12014: table 'ACCOUNT' does not contain a primary key constraint |
If we cannot re-create the primary key, Mview Log is created according to rowid.
1 2 3 | SQL> CREATE MATERIALIZED VIEW LOG ON IPTVMWC.ACCOUNT WITH ROWID; Materialized view log created. |
2. Then Fast Refresh Mview is created.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE MATERIALIZED VIEW ACCOUNT_MV_FAST SEGMENT CREATION IMMEDIATE NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH WITH ROWID ON DEMAND AS SELECT ACCOUNT_ID, ACCOUNTNAME, STATUS FROM IPTVMWC.ACCOUNT; |
Mview Fast is refreshed as follows.
1 2 3 | SQL> EXEC DBMS_MVIEW.REFRESH ('ACCOUNT_MV_FAST', 'F'); PL/SQL procedure successfully completed. |
When Mview is created, we may not want it to be refreshed with data immediately.
In this case, we create the BUILD mode as DEFERRED.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> CREATE MATERIALIZED VIEW ACCOUNT_MV_DEFERRED SEGMENT CREATION IMMEDIATE NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD DEFERRED REFRESH COMPLETE ON DEMAND AS SELECT ACCOUNT_ID, ACCOUNTNAME, STATUS FROM IPTVMWC.ACCOUNT; Materialized view created. View sorgulandığında hiçbir sonuç gelmediği görülür. SQL> SELECT COUNT(*) FROM ACCOUNT_MV_DEFERRED; COUNT(*) ---------- 0 |
In some cases, when a data change occurs in our main table, we may want the change to be reflected in Mview immediately.
In this case, we use the phrase “ON COMMIT”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SQL> CREATE MATERIALIZED VIEW ACCOUNT_MV_ONCOMMIT SEGMENT CREATION IMMEDIATE NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH ON COMMIT AS SELECT ACCOUNT_ID, ACCOUNTNAME, STATUS FROM IPTVMWC.ACCOUNT; Materialized view created. Örneğin oluşturulan view' e aşağıdaki gibi insert işlemi yapılıp commit yapıldığında sonucun arttığı görülür. SQL> SELECT COUNT(*) FROM ACCOUNT_MV_ONCOMMIT; COUNT(*) ---------- 1193403 SQL> INSERT INTO IPTVMWC.ACCOUNT VALUES ('9999998', '1', '939009998', 'aXyZ', '', '1', ''); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM ACCOUNT_MV_ONCOMMIT; COUNT(*) ---------- 1193404 |
Finally, materialized views can be created in a way that never refreshes. And these views cannot be refreshed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> CREATE MATERIALIZED VIEW ACOUNT_MV_NEV_REFRESH SEGMENT CREATION IMMEDIATE NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE NEVER REFRESH AS SELECT ACCOUNT_ID, ACCOUNTNAME, STATUS FROM IPTVMWC.ACCOUNT; Materialized view created. View sorgulandığında; SQL> SELECT COUNT(*) FROM ACOUNT_MV_NEV_REFRESH; COUNT(*) ---------- 1193405 |
Afterwards, when the insertion process is performed and the query is made again, it is seen that the number has not changed.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> INSERT INTO IPTVMWC.ACCOUNT VALUES ('9999996', '1', '939009996', 'aXyZ', '', '1', ''); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM ACOUNT_MV_NEV_REFRESH; COUNT(*) ---------- 1193405 |
Finally, when you try to refresh the view, you get an error.
1 2 3 4 5 6 7 8 9 10 11 | SQL> EXEC DBMS_MVIEW.REFRESH ('ACOUNT_MV_NEV_REFRESH', 'C'); BEGIN DBMS_MVIEW.REFRESH ('ACOUNT_MV_NEV_REFRESH', 'C'); END; * ERROR at line 1: ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view ("ACOUNT_MV_NEV_REFRESH") ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994 ORA-06512: at line 1 |
A view that has been made Never Refresh can be made refreshable again.
1 2 3 | SQL> ALTER MATERIALIZED VIEW ACOUNT_MV_NEV_REFRESH REFRESH ON DEMAND COMPLETE; Materialized view altered. |
Afterwards, when the view is executed, it is observed that the number has changed.
1 2 3 4 5 6 7 8 9 | SQL> EXEC DBMS_MVIEW.REFRESH ('ACOUNT_MV_NEV_REFRESH', 'C'); PL/SQL procedure successfully completed. SQL> SELECT COUNT(*) FROM ACOUNT_MV_NEV_REFRESH; COUNT(*) ---------- 1193406 |
