In the prior article in this two-part series, I discussed several enhancements for materialized views in Oracle 10gR2, most notably:

• Join-only materialized views (MJVs)
• Improved methods for investigating why a materialized view hasn't been “rewritten” as expected using EXPLAIN_REWRITE and TUNE_MVIEW procedures of DBMS_MVIEW
• Using trusted constraints to enhance the performance of query rewrite operations

As impressive as those enhancements are, I'll next discuss some equally impressive and powerful features in this final article in the series. I'll start off with showing how you can use Partition Change Tracking (PCT) to enable a materialized view to refresh its underlying data even more quickly by allowing it to refresh only those rows that are affected by changes in individual partitions of the underlying tables.

Partition Change Tracking

Prior to Oracle 10g, a table's partition key(s) and special partition markers (aka PMARKERs) had to be identified so that a materialized view could take advantage of partitioned change tracking for quicker refreshes. Oracle 10g enhances PCT by allowing the ROWID pseudocolumn to take the place of the partition key or PMARKER columns.

This is especially good news for materialized view joins (MJVs), the new Oracle 10g MV concept introduced and explained in the previous article in this series. Since in many cases the ROWID column is already being used within the MVJ's defining query, there is no need to modify the MVJ's definition to take advantage of this feature. If the underlying partitioned tables for a materialized view can take advantage of PCT, this metadata is also recorded in Oracle 10g's data dictionary so when a refresh for the underlying tables is requested, the refresh can easily take advantage of PCT.

Partition Change Tracking and List Partitioning

PCT works particularly well with list partitioned tables. Listing 2.1 shows the code that I used to create a new table, SH.LIST_SALES_TIME, that's list-partitioned on its MONTH column into four quarterly partitions. I then created a new materialized view, SH.MV_PCT_SALES, that takes advantage of the new partitioned table.

What happens when data is manipulated directly in SH.LIST_SALES_TIME is illustrated in Listing 2.2. Because I created a materialized view log on that table, PCT fast refresh is enabled on the SH.MV_PCT_SALES materialized view. When I update data in that underlying table, Oracle 10g automatically detects this and immediately triggers the refresh of the materialized view, but it only needs to apply the changes to the data in the affected quarter's partition. This means that a COMPLETE refresh of that materialized view is avoided.

Listing 2.3 also includes three queries and the resulting output after I've applied the updates to the SH.LIST_SALES_TIME table.

Join Dependency and PCT Refreshes

Of course, there are a few restrictions on using PCT. For starters, PCT-based refresh is available for a materialized view only if it contains a join-dependent expression on columns in at least one of its detail tables. A join-dependent expression is an expression consisting of columns from tables directly or indirectly joined through equijoins to the partitioned detail table on the partitioning key. The set of tables in the path to detail table are termed join-dependent tables. Join-dependent tables therefore allow users to create materialized views containing aggregates on some level higher than the partitioning key of the detail table.

There are some other restrictions as well:

• The COMPATIBILITY initialization parameter must be set to at least 9.0.0.0.0.
• The materialized view must reference at least one partitioned table.
• The underlying partitioned tables must be either list, range, or composite partitioned.
• Only a single column can define the partition key of the “top level” of the partitioned table.
• Either (a) the partition key column, (b) the partition marker, (c) the ROWID, or (d) the join dependent expression has to be present in the GROUP BY clause if one is used to define the materialized view.
• Either (a) a ROWID, (b) the detail table's join dependent expression, (c) partition key column, (d) the partition marker must be included in the materialized view.
• Oracle doesn't support PCT for any materialized view that refers to a view, a remote table, or contains outer joins.
• Data can only be changed on the underlying partitioned table. If you want to insure that PCT refresh will occur for a table that has a join dependent expression in the materialized view, data modifications can't occur in any of the join dependent tables.
• If a materialized view contains a UNION ALL statement, then note that PCT-based refresh is not possible.
• Finally, if either the MODEL clause or an analytic window function is used within the materialized view, then either (a) the partition key column, (b) the partition marker, (c), the ROWID, or (d) the join dependent expression has to be listed in each set of PARTITION BY statements.

Forcing a PCT-Based Refresh with DBMS_MVIEW.REFRESH()

To specifically force the refresh of a PCT-enabled materialized view, Oracle 10g adds a new value, (P)artitioned, for the METHOD argument of the DBMS_MVIEW.REFRESH() procedure. Note that if I call this procedure and specify the question mark (?) value for this argument, Oracle 10g will attempt to figure out if a PCT-based refresh is possible and if so, it will automatically request the FAST_PCT refresh method rather than a COMPLETE refresh.

In Listing 2.2, I've demonstrated how to use this feature to refresh the SH.MV_PCT_SALES PCT refresh enabled materialized view that we've been using in our current set of examples.

Partition Maintenance Operations (PMOPs)

Since a materialized view is really nothing more than a view with an underlying table, there's nothing that prohibits that underlying table from being partitioned. Oracle 10g takes advantage of this by allowing maintenance to be performed against the partitioned data subsets of a partitioned materialized view whenever that materialized view's source tables are modified via the ALTER MATERIALIZED VIEW command.

For example, if I drop an entire partition from the underlying table with the ALTER TABLE <table_name> DROP PARTITION; command, Oracle10g will immediately drop that corresponding partition from the materialized view during its next refresh. Likewise, if I issue an ALTER TABLE <table_name> TRUNCATE PARTITION; operation to completely remove a partition in an underlying partitioned table, then the corresponding data in the materialized view will be removed completely as well.

There are some restrictions on when PCT will be triggered in concert with a TRUNCATE PARTITION operation, however:

• Both the underlying partitioned table and its materialized view must be range-partitioned.
• A single partition key column must define the partitioned table's partitioning scheme.
• The partitions in both the materialized view and its underlying partitioned table must relate one-to-one.
• Because TRUNCATE is a DDL command, the partition will be removed immediately from the source table, and the data will be removed immediately from the materialized view; its removal cannot be rolled back.

Listing 2.4 shows how this feature works. I've created a second materialized view, SH.MV_PCT_PART_SALES, that's partitioned on month number identically to the SH.LIST_SALES_TIME table. I've then shown what happens when I drop or truncate a partition of the SH.LIST_SALES_TIME table. Note that the data is immediately refreshed in both the SH.MV_PCT_PART_SALES and SH.MV_PCT_SALES materialized views to reflect the change in values in the source table.

Conclusion

The addition of Partition Change Tracking (PCT) features in Oracle 10g significantly expands the ability to keep materialized views synchronized with the sources of their data, and Partition Maintenance Operations (PMOPs) offer even faster refreshes of materialized views when a materialized view is partitioned in the same fashion as its source table.

References and Additional Reading

Even though I've hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I've drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:

B14200-02 Oracle Database SQL Reference

B14214-01 Oracle Database New Features Guide

B14223-02 Oracle Database Data Warehousing Guide

B14231-01 Oracle Database Administrator's Guide

B14237-02 Oracle Database Reference

B14258-01 PL/SQL Packages and Types Reference

Previous

Back to DBAsupport.com