August 2013

In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the MAX aggregate function:

[sql light=”true”]
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
;
[/sql]

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.…

In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the SUM aggregate function:

[sql light=”true”]
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
;
[/sql]

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.…

This post introduces a series about the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only an aggregate:

[sql light=”true”]
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
;
[/sql]

Where AGG is either SUM or MAX, the most important aggregates.…