Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle 10g Materialized Views new features

Oracle Database Tips by Donald Burleson

Materialized Views (MV's) are a good way of summarizing a large number of detail rows into smaller objects. They are very useful because they pre-compute long operations. In fact, they are query results that are stored in a different object. We can use materialized views in data warehouses to increase the speed of queries on very large databases.

A materialized view definition may include a large number of aggregates, as well as many joins. Materialized views are defined or created by a query, are stored in a physical object, and follow certain refresh mechanisms. This refresh method keeps the materialized view up to date.

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In case of an ON COMMIT scenario, when a transaction commits, the materialized view is refreshed and as a result, the data is always current in a materialized view. In case of an ON DEMAND type, calling the dbms_mview package procedures refreshes the materialized view.

Materialized View Partition Tracking Change

When the source or detail tables have partitions, the refresh mechanism follows the partition change tracking (PCT) method, which identifies the partition and makes changes to the materialized views. This is a very useful and performance-enhancing methodology. It helps minimize refreshes and maximize rewrites. PCT was first introduced in the Oracle 9i database, but it only supported the range and range-hash partitioning schemes. In Oracle 10g, the PCT refresh is extended to support the list partition scheme also.

Let us look at an example to show the creation of a materialized view. Let us assume we have a table list_sales_time created as follows.

CREATE table list_sales_time (time_id, cust_id, month, week, quantity_sold, amount_sold)
partition m1 values (48, 49, 50),
partition m3 values (5, 6, 7, 8, 9, 10, 11, 12),
partition others values (default)) ;

Then, the materialized view is defined.

CREATE MATERIALIZED VIEW pct_sales_materialized view
SELECT ls.week, AVG(ls.quantity_sold) as avg,
COUNT(*) as cnt, COUNT(ls.quantity_sold) as cnt
FROM list_sales_time ls GROUP BY ls.week, ls.cust_id;

In this example, the materialized view, pct_sales_materialized view, has a base table called list_sales_time that is LIST partitioned. The column WEEK is the PCT column.

In prior releases, table partition keys and partition markers (pmarkers) are considered PCT columns at the time the materialized view is created, and are used by PCT refresh to identify a table partition. With Oracle 10g, even the ROWID is considered as a PCT column. This feature is very useful for queries containing the ROWIDs.

Join Only Materialized View (MJV) Enhancement

In prior releases, a fast refresh of materialized views with joins and aggregates (MAVs) supported the following features:

  • Self-joins.
  • Views (provided they can be flattened).
  • Remote tables in the FROM clause of the materialized view defining query.

However, these features were not supported in materialized views with joins only (MJVs). With Oracle10g, they are supported for fast refresh for MJVs also.

Nested materialized view enhancement

Previously, whenever we refreshed materialized views with refresh API (with the dbms_mview package), only a single-level data synchronization used to take place. The package procedures selected the changes from the detail tables without considering if the detail tables could also be materialized views. As a result, a nested materialized view based on stale materialized views remains stale after refresh.

With Oracle10g, the refresh mechanism is extended to support nested refresh. All the underlying materialized views of a nested materialized view are refreshed first based on dependency order, and then the nested materialized view itself is refreshed to ensure its freshness and consistency with underlying tables whenever possible.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.