What Is Oracle Materialized View

Today, I want to talk about Oracle Materialized View. What is Oracle Materialized View, it looks like a copy of a view or table. It is same as a table with row data. A MV has two advantages ;

1- We can copy the target table data to the MV. For example we can make a copy of remote DB’s table data. And this copy table (the materialized view) can detect the target tables update-insert-delete operations and updates its data in time periods. So we could make our MV data keep up to date. At the same time, we could reduce the process load on remote server.

2- This provides a great performance. For example, consider a view that uses data from several tables, and it will take a lot of effort to get the data from those few tables. However, if we create this view as MV, it will use the data copied to MV. So it won’t go and get the data from its tables.

Let’s make an example of Oracle Materialized View :

We have a view called hdk. It takes 2-3 minutes to fetch all data from tables. So we need to get data fastly and create MV. We tell “build immediate” to do it quickly. “Refresh force” identifies the refresh method. We tell “on demand” command to make the refresh in a minute ( 1/(24*60) ).

If you have any question about MV write it in comments or you can send email to me. You can have a look at my post about Make Oracle View.