Materialized View – Performance Improvement

Materialized View(MView) is the database object, which is used to store the data of query from other instances to reduce the I/O operations performed on the disc and to improve the overall performance of the query.

I/O Operation is one of the factors we need to consider while improving the performance of the query. I/O Operations are performed when we are using a DBLink to fetch the data of one instance from another instance. In Oracle Applications, usually, we fetch data from OLTP instance to ASCP instance using DBLink or vice versa.

After the Materialized View is created, there are two ways to refresh the Materialized View:

  1. Manual Refresh: MView can be refreshed on demand by using the standard package dbms_snapshot.refresh_mview
  2. Automatic Refresh: MView can be refreshed as soon as any changes are made in the table underlying the MView using “On Commit”

Types of Refresh:

  1. Complete(Full) – Whenever the base table is modified, MView will be truncated first and will be loaded with the data. As the name suggests, it is refreshed completely.
  2. Fast – Whenever the base table is modified, only the updated records are updated/inserted in the MView. We need “mvlog” file to make “Fast” refresh.
  3. Force – It will first try to do the “Fast” refresh. If for some reasons, “Fast” refresh fails then it will do the “Complete” refresh.

Syntax for MView:

refresh complete on commit –Automatic Complete refresh
enable query rewrite
SELECT deptno, dept_name, SUM(salary)
FROM department
GROUP BY deptno, dept_name;

To drop the MView, we can write:

Few Points to Remember:

  1. Names of Base table and Materialized View cannot be the same
  2. Cannot perform DML Operations on MView directly.
  3. We can use truncate and drop
  4. If we truncate base table then MView will not refresh but if we delete records from base table then MView will be refreshed
  5. To get all the MViews, we can query: SELECT * FROM users_mview
  6. If the base table is dropped then the MView is still accessible.

Differences between View and MView:

  1. A view is the stored query in the database which gets executed when it is called. It does not store any data. MView stores the data of a query.
  2. The main purpose of View is for security purpose. For eg., if we want to give SELECT access to a certain number of columns of a table then we create a View. The main purpose of MView is to improve the performance of the query i.e. to reduce the I/O Operations performed in the database.
  3. We can perform DML Operations on View whereas we cannot perform DML Operations on MView.
  4. If the base table is dropped the view is also not accessible, however, MView is accessible.

1 comments On Materialized View – Performance Improvement

Leave a reply:

Your email address will not be published.

Sliding Sidebar