In this post I will illustrate the algorithm used by Oracle (in 220.127.116.11) to fast refresh a materialized view (MV) containing only the MAX aggregate function:
create materialized view test_mv build immediate refresh fast on demand with rowid as select gby as mv_gby, count(*) as mv_cnt_star, max (dat) as mv_max_dat from test_master --where whe = 0 group by gby ;
The where clause is commented to enable fast refresh whatever type of DML occurs on the master table, in order to investigate all possible scenarios; the case having the where-clause is anywhere a sub-case of the former and we will illustrate it as well below.
As usual, the MV log is configured to "log everything":
In this post I will illustrate the algorithm used by Oracle (in 18.104.22.168) to fast refresh a materialized view (MV) containing only the SUM aggregate function:
create materialized view test_mv build immediate refresh fast on demand with rowid as select gby as mv_gby, count(*) as mv_cnt_star, sum (dat) as mv_sum_dat, count(dat) as mv_cnt_dat from test_master where whe = 0 group by gby ;
Note that count(dat) is specified - you could avoid that if column dat is constrained to be not-null (as stated in the documentation), but I'm not covering that corner case here.
The MV log is configured to "log everything":
This post introduces a series about the algorithm used by Oracle (in 22.214.171.124) to fast refresh a materialized view (MV) containing only an aggregate:
create materialized view test_mv build immediate refresh fast on demand with rowid as select gby as mv_gby, count(*) as mv_cnt_star, AGG (dat) as mv_AGG_dat, count(dat) as mv_cnt_dat from test_master where whe = 0 group by gby ;
Where AGG is either SUM or MAX, the most important aggregates.
In the next posts, I will illustrate the algorithms used to propagate conventional (not direct-load) inserts, updates and deletes on the master table; I will illustrate also the specialized versions of the algorithms used when only one type of DML has been performed (if they exist).
In this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner table without a unique constraint on the joined column(s).
To recap, now the outer slice can be composed of more than one row, for example:
and hence, both the DEL and INS step must consider (and read) the whole outer slice even if only a subset of the inner rows have been modified. This requires both more resources and a considerably more complex algorithm. Let's illustrate it (the mandatory test case is here).
The DEL macro step
This sub step (named DEL.del by me) is performed first:
In this series of posts we will discuss how Oracle refreshes materialized views (MV) containing only OUTER joins, covering only 126.96.36.199. We will use the very same scenario (MV log configuration, DML type, etc) as in the inner join case, "just" turning the inner join into an outer join:
In my current "big" project, I am building a network of nested materialized views to transform rows of one schema into rows of another (very different) schema. The former is used by the old (but still live) version of an application of ours, the latter by the new version; our idea is to incrementally (aka "fast") refresh the network daily in order to have the new schema ready when the new version goes live. We need this nework because we have only a few hours of allowed downtime, and the transformations are very complex: the MV network is going to be composed of at least 200+ MVs, each containing tens of millions of rows.
A devastating performance degradation of materialized view fast refreshes can happen in versions after 9i - and can be healed rather easily by simply setting the hidden parameter _mv_refresh_use_stats or, a bit surprisingly, by locking statistics on the logs. The problem can manifest at least in the currently-latest patchsets of 10g, 11gR1 and 11gR2 (10.2.0.4, 188.8.131.52 and 184.108.40.206), seems to hit a lot of people, and its root cause are the utilization of wrong hints by the Oracle refresh engine.
We will investigate the join-only MV case only, since this is the case I have investigated after a question by Christo Kutrovsky, factoring in some observations by Taral Desai and some Support notes; I have some clues that something similar may happen for other types of MVs.
This post investigates the improvements that have been made in 11gR2 to the fast refresh engine of materialized views (MVs) that are set to be automatically refreshed at commit time. We speak about join-only materialized views only in this post, as always with the help of a test case.
As noted in the post of mine [...]
In this post we are going to discuss some 11gR2 changes to materialized view logs that are aimed at increasing the performance of the fast-refresh engine of materialized views (MVs), especially the on-commit variant.
The MV logs, in 10gr2, now comes in two flavours: the traditional (and still the default) timestamp-based one and the brand [...]