How do you refresh materialized view

Vikash Prasad Vikash Prasad 11 1 1 bronze badge. Welcome to Stackoverflow. Please take some time to read how to write a good answer. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.

Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta. Now live: A fully responsive profile. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email.

Notify me of new posts via email. Complete Refresh The simplest form to refresh a materialized view is a Complete Refresh. Fast Refresh A more elegant and efficient way to refresh materialized views is a Fast Refresh. Share this: Twitter LinkedIn Email. Like this: Like Loading Thank you! Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:.

Email required Address never made public. Name required. Follow Following. Data Warehousing with Oracle Join other followers. Sign me up. Already have a WordPress. Log in now. The materialized view log resides in the same database and schema as its base table.

The PCT refresh method can be used if the modified base tables are partitioned and the modified base table partitions can be used to identify the affected partitions or portions of data in the materialized view. When there have been some partition maintenance operations on the base tables, this is the only incremental refresh method that can be used.

The PCT refresh removes all data in the affected materialized view partitions or affected portions of data and recomputes them from scratch. About Complete Refresh for Materialized Views. About Fast Refresh for Materialized Views.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh.

In the case of ON COMMIT , the materialized view is changed every time a transaction commits, thus ensuring that the materialized view always contains the latest data. For details, see Synchronous Refresh. There are three basic types of refresh operations: complete refresh, fast refresh, and partition change tracking PCT refresh. These basic types have been enhanced in Oracle Database 12 c , Release 1 with a new refresh option called out-of-place refresh.

Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views. When Fast Refresh is Possible. For each of these refresh options, you have two techniques for how the refresh is performed, namely in-place refresh and out-of-place refresh. The in-place refresh executes the refresh statements directly on the materialized view. The out-of-place refresh creates one or more outside tables and executes the refresh statements on the outside tables and then switches the materialized view or affected materialized view partitions with the outside tables.

Both in-place refresh and out-of-place refresh achieve good performance in certain refresh scenarios. However, the out-of-place refresh enables high materialized view availability during refresh, especially when refresh statements take a long time to finish.

Also adopting the out-of-place mechanism, a new refresh method called synchronous refresh is introduced in Oracle Database 12 c , Release 1. It targets the common usage scenario in the data warehouse where both fact tables and their materialized views are partitioned in the same way or their partitions are related by a functional dependency.

The refresh approach enables you to keep a set of tables and the materialized views defined on them to be always in sync. In this refresh method, the user does not directly modify the contents of the base tables but must use the APIs provided by the synchronous refresh package that will apply these changes to the base tables and materialized views at the same time to ensure their consistency.

The synchronous refresh method is well-suited for data warehouses, where the loading of incremental data is tightly controlled and occurs at periodic intervals. About the Out-of-Place Refresh Option. A complete refresh may be requested at any time during the life of any materialized view. The refresh involves reading the detail tables to compute the results for the materialized view. This can be a very time-consuming process, especially if there are huge amounts of data to be read and processed.

Therefore, you should always consider the time required to process a complete refresh before requesting it. There are, however, cases when the only refresh method available for an already built materialized view is complete refresh because the materialized view does not satisfy the conditions specified in the following section for a fast refresh. Most data warehouses have periodic incremental updates to their detail data. Fast refresh of your materialized views is usually efficient, because instead of having to recompute the entire materialized view, the changes are applied to the existing data.

Whe n there have been some partition maintenance operations on the detail tables, this is the only method of fast refresh that can be used. PCT-based refresh on a materialized view is enabled only if all the conditions described in " About Partition Change Tracking " are satisfied. Beginning with Oracle Datab ase 12 c Release 1, a new refresh option is available to improve materialized view refresh performance and availability. This refresh option is called out-of-place refresh because it uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table.

Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well. It also enables you to achieve a very high degree of availability because the materialized views that are being refreshed can be used for direct access and query rewrite during the execution of refresh statements. In addition, it helps to avoid potential problems such as materialized view container tables becoming fragmented over time or intermediate refresh results being seen.

In out-of-place refresh, the entire or affected portions of a materialized view are computed into one or more outside tables.

For partitioned materialized views, if partition level change tracking is possible, and there are local indexes defined on the materialized view, the out-of-place method also builds the same local indexes on the outside tables.

This refresh process is completed by either switching between the materialized view and the outside table or partition exchange between the affected partitions and the outside tables. Note that query rewrite is not supported during the switching or partition exchange operation. During refresh, the outside table is populated by direct load, which is efficient.

Types of Out-of-Place Refresh. Restrictions and Considerations with Out-of-Place Refresh. This offers better availability than in-place fast refresh. It also offers better performance when changes affect a large part of the materialized view.

This offers better availability than in-place PCT refresh. There are two different approaches for partitioned and non-partitioned materialized views. If truncation and direct load are not feasible, you should use out-of-place refresh when the changes are relatively large. If truncation and direct load are feasible, in-place refresh is preferable in terms of performance. In terms of availability, out-of-place refresh is always preferable.

An example is the following:. Out-of-place refresh has all the restrictions that apply when using the corresponding in-place refresh. In addition, it has the following restrictions:. Atomic mode is not permitted. Not allowed for materialized join views when more than one base table is modified with mixed DML statements. Out-of-place refresh requires additional storage for the outside table and the indexes for the duration of the refresh. Thus, you must have enough available tablespace or auto extend turned on.

The partition exchange in out-of-place PCT refresh impacts the global index on the materialized view. Therefore, if there are global indexes defined on the materialized view container table, Oracle disables the global indexes before doing the partition exchange and rebuild the global indexes after the partition exchange.

This rebuilding is additional overhead. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view. The only disadvantage is the time required to complete the commit will be slightly longer because of the extra processing involved.

However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table. You can define a default option during the creation of the materialized view.

Table details the refresh options. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables. Each has its own unique set of parameters. Oracle Database Advanced Replication for information showing how to use it in a replication environment.

Some parameters are used only for replication, so they are not mentioned here. The required parameters to use this procedure are:. A Boolean parameter. The alert log for the instance gives details of refresh errors. If set to FALSE , the default, then refresh stops after it encounters the first error, and any remaining materialized views in the list are not refreshed.

The following four parameters are used by the replication process. If set to TRUE , then all refreshes are done in one transaction. If set to FALSE , then each of the materialized views is refreshed non-atomically in separate transactions. Atomic refresh cannot be guaranteed when refresh is performed on nested views.

This parameter works with all existing refresh methods F , P , C ,? Multiple materialized views can be refreshed at the same time, and they do not all have to use the same refresh method. To give them different refresh methods, specify multiple method codes in the same order as the list of materialized views without commas. If the refresh method is not specified, the default refresh method as specified in the materialized view definition is used. This procedure refreshes all materialized views.

If any of the materialized views fails to refresh, then the number of failures is reported. If set to FALSE , the default, then refresh stops after it encounters the first error, and any remaining materialized views in the list is not refreshed. This parameter works with all existing refresh method F , P , C ,? For example, suppose the changes have been received for the orders table but not for customer payments. The refresh dependent procedure can be called to refresh only those materialized views that reference the orders table.

If set to TRUE , refresh all the dependent materialized views of the specified set of tables based on a dependency order to ensure the materialized views are truly fresh with respect to the underlying base tables.

To perform a full refresh on all materialized views that reference the customers table, specify:. Job queues can be used to refresh multiple materialized views in parallel. If queues are not available, fast refresh sequentially refreshes each view in the foreground process. This parameter defines the number of background job queue processes and determines how many materialized views can be refreshed concurrently. Oracle tries to balance the number of concurrent refreshes with the degree of parallelism of each refresh.

The order in which the materialized views are refreshed is determined by dependencies imposed by nested materialized views and potential for efficient refresh by using query rewrite against other materialized views See " Scheduling Refresh of Materialized Views " for details.

REFRESH is interrupted or the instance is shut down, any refresh jobs that were executing in job queue processes are requeued and continue running.

Just e-mail: and include the URL for the page. All rights reserved by Burleson. You want tips on tuning materialized views internal performance, see: Oracle materialized views and partitioning Materialized Views Tuning Materialized Views Refreshing Performance Oracle materialized views were first introduced in Oracle8, and in Oracle materialized views were enhanced to allow very fast dynamic creation of complex objects.

Without Oracle materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over: Prerequisites for using Oracle materialized views In order to use Oracle materialized views, the Oracle DBA must set special initialization parameters and grant special authority to the users of Oracle materialized views.

You start by setting these initialization parameters within Oracle to enable the mechanisms for Oracle materialized views and query rewrite, as shown here: trusted : Assumes that the Oracle materialized view is current. These additional checks include the following: An Oracle materialized view log must be present for each detail table. If there are outer joins, unique constraints must be placed on the join columns of the inner table.

Create the materialized view table. Here, we specify that the materialized view will be refreshed every two hours with the refresh fast option. Create the optimizer statistics and refresh the materialized view. Test the materialized view.