Database Reference
In-Depth Information
In this case, the fast refresh lasted 0.12 seconds. If you aren't satisfied with the performance of a fast refresh, you
should use SQL trace to investigate why it's taking too long. Then, by applying the techniques described in Chapter 13,
you might be able to speed it up by adding indexes (on the master tables, on the materialized view, or even sometimes
on the materialized view log) or by partitioning a segment.
■
There are several undocumented parameters that, depending on the version you're using and the type of
materialized view you have troubles with, might be relevant for performance. discussing these parameters goes beyond
the scope of this topic. If you have performance issues with fast refreshes, I advise you to take a look at the oracle
Support note
Master Note for Materialized View
(1353040.1), specifically to the references in the “performance Issues
with MVIeW” section.
Tip
Fast Refreshes with Partition Change Tracking
Tables that store historical data are frequently range partitioned by day, week, or month. In other words, partitioning
is based on a column that stores timing information. Therefore, it happens regularly that new partitions are
added, data is loaded into them, and older ones are dropped (it's common to keep online only a specific number
of partitions). After performing these operations, all dependent materialized views are stale and thus should be
refreshed.
The problem is that fast refreshes with materialized view logs (those described in the previous section) can't
be executed after a partition management operation such as
ADD PARTITION
or
DROP PARTITION
. If such a refresh is
attempted, the database engine raises an
ORA-32313: REFRESH FAST of <mview> unsupported after PMOPs
error.
Of course, it's always possible to execute a complete refresh. However, if there are many partitions and only one or two
of them have been modified, the refresh time might be unacceptable.
To solve this problem, fast refreshes with partition change tracking (PCT) are available. This is possible because
the database engine is able to track the staleness at partition level, and not only at table level. In other words, it's able
to skip the refresh for all the partitions that have not been altered. To use this refresh method, the materialized view
must fulfill some requirements. Basically, the database engine must be able to map the rows that are stored in the
materialized view to the base table partitions. This is possible if the materialized view contains one of the following:
•
Partition key
•
Rowid
•
Partition marker
•
Join-dependent expression
It should be obvious what the first two are; let's look at examples of the third and fourth. A partition marker is
nothing other than a partition identifier (actually, it's the data object ID associated to the segment of the partition)
generated by the
pmarker
function in the
dbms_mview
package. To generate the partition marker, the function uses the
rowid passed as a parameter. The following example, based on the
mv_refresh_pct.sql
script, shows how to create a
materialized view containing a partition marker (note that
sales
table is partitioned):
CREATE MATERIALIZED VIEW sales_mv
REFRESH FORCE ON DEMAND
AS
SELECT p.prod_category, c.country_id,
sum(quantity_sold) AS quantity_sold,
sum(amount_sold) AS amount_sold,
