Materialized Views overview

We are now ready to examine the use of Oracle materialized views (MVs).  When accessing data against any relational database, the database developer is faced with a quandary of aggregation.  As we know, extracting complex summaries and aggregations from a database can clause repeated large-table full-table scans against the database.  For very large systems, these kinds of large queries can run for many hours.

 

So how can we provide sub-second response time when the queries may run for hours?

 

The answer is with Oracle materialized views.  An Oracle materialized view allows us to pre-summarize information and store inside Oracle tables.  With Oracle's query rewrite facility enabled, Oracle will detect queries that can use the materialized views and automatically re-write the SQL to reference the materialized view.

 

The query optimizer can use materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables, which should result in a significant performance gain.

 

This revolutionary technique can be used to take ordinary SQL queries down from hours to sub-second response time. This illusion of instantaneous response time is achieved by pre-summarizing the data.

 

However, there is a downside to materialized views.  Because the materialized views are derived from subsets of the Oracle data, the information and the materialized views may become stale as soon as additional data is added into our database. 

 

Hence, the Oracle database provides a refresh mechanism for materialized views.  The Oracle professional can specify that the materialized views be refreshed instantly, every 10 minutes, every day, and so on, depending on the volatility of the data.  Here is an example:

 

 

In the above example, the MV is re-created every 1/24 of a day (once per hour).

 

This refresh interval gives the database developer complete control over the refresh interval for the materialized views, and allows them to take long-running expensive SQL queries and make then run super-fast.

 

Prior to Oracle8i, DBAs who have used summaries spent a significant amount of time manually to create them, identifying which ones to create, index them, update them, and advise their users on which ones to use.

 

To see how MV’s work, here is an actual example of a manual aggregation from a real data warehouse:

 

Once the MV is defined, Oracle will automatically check the data dictionary to see of an MV can be used to service the query.

 

 

If so, Oracle will dynamically re-write your query to reference the MV, and you will be able to see this change when you gather the execution plan for your SQL.