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;