Database Reference
In-Depth Information
When to Use It
Materialized views are redundant access structures. Like all redundant access structures, they're useful for accessing
data efficiently, but they impose an overhead to keep them up-to-date. If you compare materialized views to indexes,
both the improvement and the overhead of materialized views may be much higher than those of indexes. Clearly, the
two concepts are aimed at solving different problems. Simply put, you should use materialized views only if the pros
of improving data access exceed the cons of managing redundant copies of the data (such as indexes, of course).
In general, I see two uses of materialized views:
•
To improve the performance of large aggregations and/or joins for which the ratio between
the number of logical reads and the number of returned rows is very high.
•
To improve the performance of single-table accesses that are neither performed efficiently
with a full table scan nor performed efficiently with an index range scan. Basically, these are
accesses with an average selectivity that would require partitioning, but if it isn't possible to
take advantage of partitioning (Chapter 13 discusses when this isn't possible), materialized
views might be helpful.
Materialized views are commonly used in data warehouses to build stored aggregates. There are two reasons
for this. First, data is mostly read-only; therefore, the overhead of refreshing materialized views can be minimized
and segregated in time windows while the database is dedicated to modifying the tables only. Second, in such
environments, the improvement may be huge. In fact, without materialized views, it's common to see queries based
on large aggregates or joins that require an unacceptable amount of resources to be processed.
Even if data warehouses are the primary environment where materialized views are used, I have been successful in
implementing them in OLTP systems as well. This may be beneficial for tables that are frequently queried and undergo,
in comparison, relatively few modifications. In such environments, to refresh materialized views, it's common to use
fast refreshes on commit in order to guarantee subsecond refresh times and always-fresh materialized views.
Pitfalls and Fallacies
Because fast refreshes aren't always faster than complete refreshes, you shouldn't use them in all situations. One
specific case is when lots of data is modified in the base tables. In addition, you shouldn't underestimate the overhead
of maintaining the materialized view log while modifying the base tables. Hence, you should assess the pros and cons
of using fast refreshes carefully.
When creating a materialized view log, you must be very careful with the use of commas. Can you identify what's
wrong with the following SQL statement?
SQL> CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE,
2 (prod_id, prod_category) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE,
*
ERROR at line 1:
ORA-12026: invalid filter column detected
The problem is the comma between the keyword
SEQUENCE
and the filter list (in other words, the list of columns
between brackets). If the comma is present, the option
PRIMARY KEY
is implied, and that option can't be specified in
this case because the primary key (the
prod_id
column) is already in the filter list. The following is the correct SQL
statement. Notice that only the comma has been removed:
SQL> CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE
2 (prod_id, prod_category) INCLUDING NEW VALUES;
