Currently browsing

August 2013

Fast refresh of aggregate-only materialized views with MAX – algorithm

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:

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

Fast refresh of aggregate-only materialized views with SUM – algorithm

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:

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

Fast refresh of aggregate-only materialized views – introduction

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:

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

Bitnami