Materialized Views: Nested Materialized Views

Sometimes a single materialized view will not meet our requirements. For example, given this base table

select t_key, amt, key
from t2
order by t_key, amt, key ;

     T_KEY        AMT        KEY
---------- ---------- ----------
         1        100         10
         1        200         30
         1        300         20
         2        150         50
         2        250         40

say we wanted a fast refreshable materialized view defined with the following query.

select
  t_key    t_key ,
  max(amt) amt_max ,
  max(key) keep ( dense_rank last order by amt ) as t2_key_of_amt_max
from
  t2
group by
  t_key
;

     T_KEY    AMT_MAX T2_KEY_OF_AMT_MAX
---------- ---------- -----------------
         1        300                20
         2        250                40

(T2_KEY_OF_AMT_MAX identifies the KEY value associated with the highest AMT value in each group.)

As always the first step is to create a materialized view log on T2.

create materialized view log on t2
  with
    rowid ,
    sequence
    ( key, t_key, amt )
  including new values
;

Now let's see what the MY_MV_CAPABILITIES utility (created in topic DBMS_MVIEW.EXPLAIN_MVIEW) tells us about our query.

set long 5000

select
  my_mv_capabilities(
'select
  t_key    t_key ,
  max(amt) amt_max ,
  max(key) keep ( dense_rank LAST order by amt ) as t2_key_of_amt_max
from
  t2
group by
  t_key'
  , 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

Not Capable of:

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT
    aggregate function nested within an expression

  REFRESH_FAST_AFTER_ONETAB_DML
    see the reason why REFRESH_FAST_AFTER_INSERT is disabled

  REFRESH_FAST_AFTER_ONETAB_DML
    mv uses the MIN or MAX aggregate functions

  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

Though not entirely obvious from the report, it turns out our query is not fast refreshable because the LAST aggregate function which we used to implement T2_KEY_OF_AMT_MAX is not one of the fast refreshable aggregates SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX (see Restrictions on Fast Refresh on Materialized Views with Aggregates).

Let's try writing the query using a subquery instead of LAST.

select
  my_mv_capabilities(
'select
  t_key    t_key ,
  max(amt) amt_max ,
  max(key) as t2_key_of_amt_max
from
  t2
where
  ( t_key, t2.amt ) in
    (
      select t_key, max(amt)
      from   t2
      group by t_key
    )
group by
  t_key'
  , 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

Not Capable of:

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT
    subquery in mv

  REFRESH_FAST_AFTER_ONETAB_DML
    see the reason why REFRESH_FAST_AFTER_INSERT is disabled

  REFRESH_FAST_AFTER_ONETAB_DML
    mv uses the MIN or MAX aggregate functions

  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

It looks like a subquery will not work either. Perhaps an analytic approach will work?

select
  my_mv_capabilities(
'select distinct
  t_key    t_key ,
  max( amt ) over ( partition by t_key ) as amt_max ,
  last_value( key )
    over
    (
      partition by t_key
      order by amt
      range between unbounded preceding and unbounded following
    )
    as t2_key_of_amt_max
from
  t2'
  , 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

Not Capable of:

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT
    DISTINCT clause in select list in mv

  REFRESH_FAST_AFTER_INSERT
    DISTINCT clause in select list in mv

  REFRESH_FAST_AFTER_INSERT
    window function in mv

  REFRESH_FAST_AFTER_ONETAB_DML
    see the reason why REFRESH_FAST_AFTER_INSERT is disabled

  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

This last approach did not work either, which is a bit of a relief actually since the technique is rather crass. We need to rethink our approach.

Since deriving the desired result set is conceptually a three step process

find the highest AMT value
find the highest KEY value per AMT
join the results of steps (1) and (2) together on the AMT column

perhaps three separate materialized views would work? The materialized views for steps 1 and 2, which we will call MV1 and MV2, can be based on table T2 and can be refreshed independently of each other. However the materialized view for step 3, which we will call MV3, will need to be based on MV1 and MV2 and will need to refresh after they do.

Fortunately Oracle allows for a materialized view like MV3 and automatically manages the refresh order when all three views are refreshable on commit. Materialized views like MV3 are called "Nested Materialized Views". Note the term "Nested Materialized View" does not refer to MV1 and MV2, even though they could be thought of as being "nested" within MV3.

Restrictions and Recommendations

As always, before creating a type of materialized view we have not tried before we must be aware of its restrictions. For nested materialized views they are these.

The base materialized views must contain joins or aggregates.
The defining query must contain joins or aggregates.
All base objects, whether they are tables or materialized views, must each have materialized view logs.
If REFRESH FAST is specified then all materialized views in any chain related to the materialized view must also specify REFRESH FAST.

Note that all base objects in a nested materialized view, regardless of whether they are tables or materialized views, are treated as tables.

We are now ready to craft our three step solution.

create materialized view MV1
  refresh fast on commit
  as
    select
      t_key ,
      max(amt)   amt_max ,
      count(amt) amt_count ,
      count(*)   row_count
    from t2
    group by t_key
;

create materialized view log on mv1
  with
    rowid ,
    sequence
    ( t_key, amt_max, amt_count, row_count )
  including new values
;

select
  my_mv_capabilities( 'MV1', 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT

  REFRESH_FAST_AFTER_ANY_DML

create materialized view MV2
  refresh fast on commit
  as
    select
      t_key ,
      amt ,
      max(key)   max_key_per_amt ,
      count(*)   row_count
    from t2
    group by t_key, amt
;

create materialized view log on mv2
  with
    rowid ,
    sequence
    ( t_key, max_key_per_amt, row_count )
  including new values
;

select
  my_mv_capabilities( 'MV2', 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT

  REFRESH_FAST_AFTER_ANY_DML

create materialized view MV3
  refresh fast on commit
  as
    select
      mv1.t_key ,
      mv1.amt_max ,
      mv2.max_key_per_amt as t2_key_of_amt_max ,
      mv1.rowid mv1_rowid ,
      mv2.rowid mv2_rowid
    from  mv1, mv2
    where mv1.t_key = mv2.t_key
    and   mv1.amt_max = mv2.amt
;

select
  my_mv_capabilities( 'MV3', 'REFRESH' ) as mv_report
from dual ;

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT

  REFRESH_FAST_AFTER_ONETAB_DML

  REFRESH_FAST_AFTER_ANY_DML

We finally have a fast refreshable materialized view solution. Let's confirm that MV3, the nested one, contains the correct results.

select t_key, amt_max, t2_key_of_amt_max
from mv3
order by t_key ;

     T_KEY    AMT_MAX T2_KEY_OF_AMT_MAX
---------- ---------- -----------------
         1        300                20
         2        250                40

Good. It matches the results returned by the first query we tried which used the LAST function. Now let's put all three materialized views through their paces. First we perform a few mixed DML transactions.

insert into t2 values ( 60, 3, 450 );
insert into t2 values ( 70, 3, 550 );

update t2 set amt = 300 where key = 30 ;
commit;

delete from t2 where key = 70 ;
update t2 set amt = 650 where key = 60 ;
commit;

select t_key, amt, key
from t2
order by t_key, amt, key ;

     T_KEY        AMT        KEY
---------- ---------- ----------
         1        100         10
         1        300         20
         1        300         30
         2        150         50
         2        250         40
         3        650         60

Now we check MV3 to see if it contains the correct info.

select t_key, amt_max, t2_key_of_amt_max
from mv3
order by t_key ;

     T_KEY    AMT_MAX T2_KEY_OF_AMT_MAX
---------- ---------- -----------------
         1        300                30
         2        250                40
         3        650                60

It does. Mission accomplished.

Cleanup

drop materialized view mv1 ;
drop materialized view mv2 ;
drop materialized view mv3 ;

drop materialized view log on t2 ;

delete t2 ;
insert into t2 select * from t2_backup ;
commit;

SQL Snippets ™: Tutorials for Oracle Developers

Materialized Views

Nested Materialized Views

Restrictions and Recommendations

Cleanup

Linking to SQL Snippets ™