Oracle 10g Materialized Views new
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
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.
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
BUILD IMMEDIATE REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
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
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.
Materialized View (MJV) Enhancement
In prior releases, a fast
refresh of materialized views with joins and aggregates (MAVs) supported the
- Views (provided they can
- 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.
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
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