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 BY LIST (week)(
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
BUILD IMMEDIATE REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
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.