However, fast refresh is able to perform significant optimizations in its processing if it detects that only inserts or deletes have been done to the tables, such as:.
If possible, refresh should be performed after each type of data change as shown earlier rather than issuing only one refresh at the end. If that is not possible, restrict the conventional DML to the table to inserts only, to get much better refresh performance. Avoid mixing deletes and direct loads. For ON COMMIT materialized views, where refreshes automatically occur at the end of each transaction, it may not be possible to isolate the DML statements, in which case keeping the transactions short will help.
However, if you plan to make numerous modifications to the detail table, it may be better to perform them in one transaction, so that refresh of the materialized view is performed just once at commit time rather than after each update. For large loads or refresh, enabling parallel DML helps shorten the length of time for the operation.
You can refresh your materialized views fast after partition maintenance operations on the detail tables. Partitioning the materialized view also helps refresh performance as refresh can update the materialized view using parallel DML. For example, assume that the detail tables and materialized view are partitioned and have a parallel clause.
The following sequence would enable Oracle to parallelize the refresh of the materialized view. Set the number of job queue processes greater than the number of processors. If job queues are enabled and there are many materialized views to refresh, it is faster to refresh all of them in a single command than to call them individually.
The best refresh method is chosen. If a fast refresh cannot be done, a complete refresh is performed. Refresh all the materialized views in a single procedure call. This gives Oracle an opportunity to schedule refresh of all the materialized views in the right order taking into account dependencies imposed by nested materialized views and potential for efficient refresh by using query rewrite against other materialized views.
If a materialized view contains joins but no aggregates, then having an index on each of the join column rowids in the detail table enhances refresh performance greatly, because this type of materialized view tends to be much larger than materialized views containing aggregates. For example, consider the following materialized view:.
Partitioning is highly recommended, as is enabling parallel DML in the session before invoking refresh, because it greatly enhances refresh performance. This type of materialized view can also be fast refreshed if DML is performed on the detail table. It is recommended that the same procedure be applied to this type of materialized view as for a single table aggregate.
This is because Oracle Database can perform significant optimizations if it detects that only one type of change has been done. Also, Oracle recommends that the refresh be invoked after each table is loaded, rather than load all the tables and then perform the refresh.
Oracle therefore recommends that you do not perform direct-path and conventional DML to other tables in the same transaction because Oracle may not be able to optimize the refresh phase. For example, the following is not recommended:. Also, try not to mix different types of conventional DML statements if possible.
This would again prevent using various optimizations during fast refresh. For example, try to avoid the following:. If many updates are needed, try to group them all into one transaction because refresh is performed just once at commit time, rather than after each update. In a data warehousing environment, assuming that the materialized view has a parallel clause, the following sequence of steps is recommended:. All underlying objects are treated as ordinary tables when refreshing materialized views.
In other words, Oracle builds a partially ordered set of materialized views and refreshes them such that, after the successful completion of the refresh, all the materialized views are fresh.
This can be achieved by invoking the refresh procedure against the materialized view at the top of the nested hierarchy and specifying the nested parameter as TRUE. If a refresh fails during commit time, the list of materialized views that has not been refreshed is written to the alert log, and you must manually refresh them along with all their dependent materialized views. You can use fast refresh for materialized views that use the UNION ALL operator by providing a maintenance column in the definition of the materialized view.
The following example illustrates how to use this clause:. The materialized view refresh automatically uses the commit SCN-based materialized view log to save refresh time. When a materialized view is created on both base tables with timestamp-based materialized view logs and base tables with commit SCN-based materialized view logs, an error ORA is raised stating that materialized view logs are not compatible with each other for fast refresh.
After you have performed a load or incremental load and rebuilt the detail table indexes, you must re-enable integrity constraints if any and refresh the materialized views and materialized view indexes that are derived from that detail data.
An important decision to make before performing a refresh operation is whether the refresh needs to be recoverable. Because materialized view data is redundant and can always be reconstructed from the detail tables, it might be preferable to disable logging on the materialized view. A major maintenance component of a data warehouse is synchronizing refreshing the materialized views when the detail data changes.
Partitioning the underlying detail tables can reduce the amount of time taken to perform the refresh task. This is possible because partitioning enables refresh to use parallel DML to update the materialized view. Also, it enables the use of partition change tracking. You now have the option of using an addition to fast refresh known as partition change tracking PCT refresh.
For PCT to be available, the detail tables must be partitioned. The partitioning of the materialized view itself has no bearing on this feature. If PCT refresh is possible, it occurs automatically and no user intervention is required in order for it to occur.
The table times is not a partitioned table. Note that the times table is not partitioned and hence can never allow for PCT refresh. Oracle Database applies PCT refresh if it can determine that the materialized view has sufficient information to support PCT for all the updated tables.
See " Analyzing Materialized View Capabilities " for information on how to use this procedure and also some details regarding PCT-related views. Fast refresh automatically performs a PCT refresh as it is the only fast refresh possible in this scenario. However, fast refresh will not occur if a partition maintenance operation occurs when any update has taken place to a table on which PCT is not enabled.
Then, the SPLIT partition operation to the sales table is performed, but before the materialized view refresh occurs, records are inserted into the times table. The materialized view is not fast refreshable because DML has occurred to a table on which PCT fast refresh is not possible. To avoid this occurring, Oracle recommends performing a fast refresh immediately after any partition maintenance operation on detail tables for which partition tracking fast refresh is available.
A common situation in a data warehouse is the use of rolling windows of data. In this case, the detail table and the materialized view may contain say the last 12 months of data. Every month, new data for a month is added to the table and the oldest month is deleted or maybe archived. PCT refresh provides a very efficient mechanism to maintain the materialized view in this case.
ETL Extraction, Transformation and Loading is done on a scheduled basis to reflect changes made to the original source system. During this step, you physically insert the new, clean data into the production data warehouse schema, and take all of the other steps necessary such as building indexes, validating constraints, taking backups to make this new data available to the end users.
Once all of this data has been loaded into the data warehouse, the materialized views have to be updated to reflect the latest data. The partitioning scheme of the data warehouse is often crucial in determining the efficiency of refresh operations in the data warehouse load process. In fact, the load process is often the primary consideration in choosing the partitioning scheme of data warehouse tables and indexes. The partitioning scheme of the largest data warehouse tables for example, the fact table in a star schema should be based upon the loading paradigm of the data warehouse.
Most data warehouses are loaded with new data on a regular schedule. For example, every night, week, or month, new data is brought into the data warehouse. The data being loaded at the end of the week or month typically corresponds to the transactions for the week or month. In this very common scenario, the data warehouse is being loaded by time.
This suggests that the data warehouse tables should be partitioned on a date column. In our data warehouse example, suppose the new data is loaded into the sales table every month. Furthermore, the sales table has been partitioned by month.
These steps show how the load process proceeds to add the data for a new month January to the table sales. This includes referential integrity constraints. A typical constraint would be:. The creation of the constraint with ENABLE clause would cause the creation of a unique index, which does not match a local index structure of the partitioned table. You must not have any index structure built on the nonpartitioned table to be exchanged for existing global indexes of the partitioned table.
The exchange command would fail. In order to add this new data to the sales table, you must do two things. First, you must add a new partition to the sales table.
This adds an empty partition to the sales table:. This exchanges the new, empty partition with the newly loaded table. This automatically maintains your global index structures as part of the partition maintenance operation and keep them accessible throughout the whole process. If there were only foreign-key constraints, the exchange operation would be instantaneous.
See Synchronous Refresh for more information. The benefits of this partitioning technique are significant. First, the new data is loaded with minimal resource utilization. The new data is loaded into an entirely separate table, and the index processing and constraint processing are applied only to the new partition.
If the sales table was 50 GB and had 12 partitions, then a new month's worth of data contains approximately four GB. Only the new month's worth of data must be indexed. None of the indexes on the remaining 46 GB of data must be modified at all. This partitioning scheme additionally ensures that the load processing time is directly proportional to the amount of new data being loaded, not to the total size of the sales table. Second, the new data is loaded with minimal impact on concurrent queries.
Therefore, none of the existing data or indexes of the sales table is affected during this data refresh process. The sales table and its indexes remain entirely untouched throughout this refresh process. With this refresh method, only the changes since the last refresh are applied to the materialized view.
But in most cases, this method is much faster than a Complete Refresh. An important precondition for a Fast Refresh is a materialized view log on each of the base tables that are referenced in the materialized view.
All columns that are used in the query must be added to the materialized view log. If multiple materialized views are created, only one materialized view log per base table is required, with all columns that are used in at least one of the materialized views.
So, three materialized view logs must be created:. In the next step, a materialized view is created. It aggregates sales data per product category and calendar year. The goal is to make this materialized view Fast Refreshable. SUM s. FROM sales s,. WHERE s. AND s. This is the frustrating part of using materialized views: There are several preconditions to enable Fast Refresh, and if only one of them is missing, the Fast Refresh method does not work.
All the restrictions on Fast Refresh are listed in the Oracle documentation. Before the first usage of the explain procedure, this table must be created with the script utlxmv. With this information, we can recreate the materialized view with the required expressions:. COUNT s. By applying many powerful algorithms, these queries can be processed at unprecedented speed and scale. This enables OLAP tools and analytic applications to use a consistent style of queries to handle the most complex questions.
Consider the following analytic query. It consists of a hierarchical cube query nested in a slicing query. The inner hierarchical cube specified defines a simple cube, with two dimensions and four levels in each dimension.
The outer query applies the constraints needed for our specific query, limiting Division to a value of 25 and Month to a value of representing January in this case. In conceptual terms, it slices a small chunk of data from the cube. The outer query's constraint on the GID column, indicated in the query by gid-for-division-month would be the value of a key indicating that the data is grouped as a combination of division and month. Oracle removes unneeded aggregation groups from query processing based on the outer query conditions.
The outer conditions of the previous query limit the result set to a single group aggregating division and month. Any other groups involving year , month , brand , and item are unnecessary here. The group pruning optimization recognizes this and transforms the query into:.
The bold items highlight the changed SQL. Because the query now requests just one group, fifteen out of sixteen groups are removed from the processing, greatly reducing the work. For a cube with more dimensions and more levels, the savings possible through group pruning can be far greater.
While the Oracle optimizer has simplified the previous query to a simple GROUP BY , faster response times can be achieved if the group is precomputed and stored in a materialized view.
Because OLAP queries can ask for any slice of the cube many groups may need to be precomputed and stored in a materialized view. This is discussed in the next section. OLAP requires fast response times for multiple users, and this in turn demands that significant parts of an OLAP cube be precomputed and held in materialized views. Oracle9 i enables great flexibility in the use of materialized views for OLAP.
Data warehouse designers can choose exactly how much data to materialize. A data warehouse can have the full OLAP cube materialized. While this will take the most storage space, it ensures quick response for any query within the cube. On the other hand, a warehouse could have just partial materialization, saving storage space, but allowing only a subset of possible queries to be answered at highest speed.
If an OLAP environment's queries cover the full range of aggregate groupings possible in its data set, it may be best to materialize the whole hierarchical cube. This means that each dimension's aggregation hierarchy is precomputed in combination with each of the other dimensions.
Naturally, precomputing a full hierarchical cube requires more disk space and higher creation and refresh times than a small set of aggregate groups. The trade-off in processing time and disk space versus query performance needs to be considered before deciding to create it.
An additional possibility you could consider is to use data compression to lessen your disk space requirements. This creates a complete hierarchical cube stored in a list-partitioned materialized view. This creates a partial hierarchical cube stored in a list-partitioned materialized view. Materialized views with multiple aggregate groups will give their best performance when partitioned appropriately.
By partitioning the materialized views this way, you enable partition pruning for queries rewritten against this materialized view: only relevant aggregate groups will be accessed, greatly reducing the query processing cost. You should consider data compression when using highly redundant data, such as tables with many foreign keys.
Oracle provides some support for materialized views whose defining query involves set operators. Materialized views with set operators can now be created enabled for query rewrite. Query rewrite with such materialized views is supported using full exact text match. Further, the materialized view must include a constant column known as a UNION ALL marker that has a distinct value in each query block, which, in the following example, is columns 1 marker and 2 marker.
A couple of things can be noted in this example. The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements.
Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitmap index is defined on each materialized view key column. In the case of materialized views containing only joins using fast refresh, Oracle recommends that indexes be created on the columns that contain the rowids to improve the performance of the refresh operation.
Chapter 21, "Using Parallel Execution" for further details. Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition.
A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated.
However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that privilege has now been granted to the owner, you should use the following statement to revalidate the materialized view:.
If you continue to get a privilege error while trying to create a materialized view and you believe that all the required privileges have been granted, then the problem is most likely due to a privilege not being granted explicitly and trying to inherit the privilege from a role instead. The owner of the materialized view must have explicitly been granted SELECT access to the referenced tables if the tables are in a different schema. All other changes are achieved by dropping and then re-creating the materialized view.
This compile process is quick, and allows the materialized view to be used by query rewrite again. For example:.
If the materialized view was prebuilt on a table, then the table is not dropped, but it can no longer be maintained with the refresh mechanism or used by query rewrite. Alternatively, you can drop a materialized view using Oracle Enterprise Manager.
In particular, this procedure enables you to determine:. Using this procedure is straightforward. Note that you must run the utlxmv. The script is found in the admin directory. An explanation of the various capabilities is in Table , and all the possible messages are listed in Table An optional parameter.
The name of an existing materialized view or the query definition of a potential materialized view you want to analyze. You can use the utlxmv. First, create the materialized view. If a capability is not possible for more than one reason, a row is displayed for each reason. If this capability is possible, Partition Change Tracking is possible on at least one detail relation.
If this capability is not possible, PCT is not possible with any detail relation referenced by the materialized view. If this capability is possible, complete refresh of the materialized view is possible. If this capability is possible, fast refresh is possible at least under certain circumstances. If this capability is possible, at least full text match query rewrite is possible. If this capability is not possible, no form of query rewrite is possible. If this capability is possible, it is possible with respect to a particular partitioned table in the top level FROM list.
PCT-based fast refresh generally does not perform as well as fast refresh from a materialized view log. If this capability is possible, fast refresh from a materialized view log is possible at least in the case where the updates are restricted to INSERT operations; complete refresh is also possible.
If this capability is not possible, no form of fast refresh from a materialized view log is possible. If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation, provided all update operations are performed on a single table. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations are performed on multiple tables.
If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation or the number of tables updated. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations other than INSERT affect multiple tables.
If this capability is possible, fast refresh using PCT is possible. Generally, this means that refresh is possible after partition maintenance operations on those detail tables where PCT is indicated as possible. If this capability is possible, full text match query rewrite is possible. If this capability is not possible, full text match query rewrite is not possible. If this capability is possible, at least full and partial text match query rewrite are possible.
If this capability is not possible, at least partial text match query rewrite and general query rewrite are not possible. If this capability is possible, all query rewrite capabilities are possible, including general query rewrite and full and partial text match query rewrite. If this capability is not possible, at least general query rewrite is not possible. For PCT capability only: [ owner. Name of the first different node, or NULL if the first different node is local. Materialized Views for Data Warehouses In data warehouses, you can use materialized views to precompute and store aggregated data such as the sum of sales.
Materialized Views for Distributed Computing In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. Note: The techniques shown in this chapter illustrate how to use materialized views in data warehouses.
See Also: Chapter 9, "Dimensions". See Also: Chapter 22, "Query Rewrite" for details about integrity levels. Thanks for sharing - though this is a bit convoluted; it wouldn't be my first choice Team, was looking into the product docs for the "refresh fast with primary key for update" by dont see that available.
February 14, - am UTC. That's because it was deprecated in February 03, - pm UTC. A reader, February 04, - am UTC. Same reason a table does not create or replace. Mviews are not simple views but have a table to hold the data as well replication. Hope it clarifies. More to Explore.
0コメント