In addition to materialized views based on join queries, materialized views containing aggregate functions are also possible. Here is a simple example.
select * from t2 order by key ;
KEY T_KEY AMT ---------- ---------- ---------- 10 1 100 20 1 300 30 1 200 40 2 250 50 2 150
create materialized view mv as select t_key t_key , SUM(AMT) AMT_SUM from t2 group by t_key ; select * from mv order by t_key ;
T_KEY AMT_SUM ---------- ---------- 1 600 2 400
REFRESH FAST
For a materialized view with only aggregates (no joins, unions, subqueries, etc.) to be fast refreshable certain restrictions beyond the General Restrictions on Fast Refresh must be met. These additional restrictions are fully documented at Restrictions on Fast Refresh on Materialized Views with Aggregates. For our current test case the most significant restrictions are these.
all base tables must have materialized view logs that:
- "Contain all columns from the table referenced in the materialized view."
- "Specify with ROWID and INCLUDING NEW VALUES."
- "Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates."
aggregates in the defining query must be either SUM, COUNT, AVG, STDDEV, VARIANCE, MIN, or MAX
the defining query's SELECT clause must contain all the columns listed in the GROUP BY clause
In addition to these restrictions some additional columns may be required in the defining query to allow it to be fast refreshable in all cases. The table below summarized these requirements.
Aggregate | Additional Aggregates Required | Optional Aggregates | Note |
---|---|---|---|
COUNT(expr) | COUNT(*) | ||
MIN(expr) | COUNT(*) | defining query must have no WHERE clause | |
MAX(expr) | COUNT(*) | defining query must have no WHERE clause | |
SUM(expr) | COUNT(*) COUNT(expr) | ||
SUM(col) | COUNT(*) | "col" must have a NOT NULL constraint | |
AVG(expr) | COUNT(*) COUNT(expr) | SUM(expr) | |
STDDEV(expr) | COUNT(*) COUNT(expr) SUM(expr) | SUM(expr*expr) | |
VARIANCE(expr) | COUNT(*) COUNT(expr) SUM(expr) | SUM(expr*expr) |
(For insert-only materialized views see Table 8-2 Requirements for Materialized Views with Aggregates.)
Oracle recommends including the Optional Aggregates expressions to obtain the most efficient and accurate fast refresh of the materialized view.
Recommendations
The recommendation about gathering statistics that we saw in the Join Queries topic also applies to materialized views with aggregates.
"After you create the materialized view, you must collect statistics on it using the DBMS_STATS package. Oracle Database needs the statistics generated by this package to optimize query rewrite." -- from CREATE MATERIALIZED VIEW.
Additionally we also expect that our GROUP BY columns will often be specified in WHERE or JOIN clauses. To improve the performance of such queries we will therefore add indexes to our materialized view's GROUP BY columns.
The Prototype
Applying these restrictions and recommendations to our test case above yields the following prototypical materialized view with aggregates. Whenever I need to create this type of materialized view in an application I use the code below as a starting point to remind me of the requirements.
drop materialized view mv ; create materialized view log on t2 with rowid, sequence ( t_key, amt ) including new values ; create materialized view mv refresh fast on commit enable query rewrite as select t_key , sum(amt) as amt_sum , count(*) as row_count , count(amt) as amt_count from t2 group by t_key ; create index mv_i1 on mv ( t_key ) ; execute dbms_stats.gather_table_stats( user, 'MV' ) ;
Whenever we create a fast refreshable view we should use our EXPLAIN_MVIEW utility, MY_MV_CAPABILITIES, to confirm it can be refreshed in all required situations.
set long 5000 select my_mv_capabilities( 'MV', '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
Now let's test drive our new MV. First, here are MV's initial contents.
select * from mv order by t_key ;
T_KEY AMT_SUM ROW_COUNT AMT_COUNT ---------- ---------- ---------- ---------- 1 600 3 3 2 400 2 2
Now let's do some DML on the base table and see the effect on MV.
insert into t2 values ( 60, 3, 300 ) ; update t2 set amt = 0 where t_key = 2 ; commit; select * from mv order by t_key ;
T_KEY AMT_SUM ROW_COUNT AMT_COUNT ---------- ---------- ---------- ---------- 1 600 3 3 2 0 2 2 3 300 1 1
Both changes are reflected in MV, as expected.
Query Rewrite
Materialized views containing aggregates can be used by the query rewrite facility (see ENABLE QUERY REWRITE).
alter materialized view mv enable query rewrite ; select my_mv_capabilities( 'MV', 'REWRITE' ) as mv_report from dual ;
MV_REPORT -------------------------------------------------------------------------------- Capable of: REWRITE REWRITE_FULL_TEXT_MATCH REWRITE_PARTIAL_TEXT_MATCH REWRITE_GENERAL
Gotcha - Insert-Only Materialized Views On Commit
We know that COUNT(*), and sometimes COUNT(expr), must be included in our materialized views for them to be fast refreshable in all cases, but what happens if we do not include these columns? Let's find out.
create materialized view mv2 refresh fast on commit enable query rewrite as select t_key , sum(amt) as amt_sum -- count(*) as row_count , -- count(amt) as amt_count from t2 group by t_key ; select * from mv2 order by t_key ;
T_KEY AMT_SUM ---------- ---------- 1 600 2 0 3 300
Let's try an INSERT.
insert into t2 values ( 70, 3, 900 ) ; commit ; select * from mv2 order by t_key ;
T_KEY AMT_SUM ---------- ---------- 1 600 2 0 3 1200
Looks good. The view was fast refreshed after the transaction committed.
In topic REFRESH FAST Categories we saw how an insert-only ON DEMAND materialized view similar to this one raised an error when we attempted to fast refresh it manually after a DELETE transaction. Let's see how our ON COMMIT version behaves after a DELETE.
delete from t2 where t_key = 1 ; 3 rows deleted. commit; Commit complete.
select * from mv2 order by t_key ;
T_KEY AMT_SUM ---------- ---------- 1 600 2 0 3 1200
Oops, all the rows for T_KEY = 1 were deleted from T2 but the group still appears in MV2. The materialized view did not refresh on commit and no errors were generated. Let's try synchronizing MV2 manually using DBMS_MVIEW.REFRESH.
execute DBMS_MVIEW.REFRESH( 'MV2', 'c' ) select * from mv2 order by t_key ;
T_KEY AMT_SUM ---------- ---------- 2 0 3 1200
That's a little better. So we've confirmed we have another insert-only materialized view, except this time we won't get any warnings or errors if a commit fails to trigger a fast refresh. When I first learned materialized views I stumbled across this behaviour by accident and found it puzzling. After all, when one creates a materialized view specifying that it should REFRESH FAST ON COMMIT it seems reasonable to assume it will always refresh fast on commit. The manual page for CREATE MATERIALIZED VIEW did not mention insert-only materialized views so I had no clue to their existence, until I re-read the page a third time and followed up on this seemingly inconsequential little comment.
"(The REFRESH clause) only sets the default refresh options. For instructions on actually implementing the refresh, refer to Oracle Database Advanced Replication and Oracle Database Data Warehousing Guide."
-- from CREATE MATERIALIZED VIEW
This eventually lead me to learn about insert-only refreshing and how indispensable the DBMS_MVIEW.EXPLAIN_MVIEW utility is when working with fast refreshable materialized views. Let's see what DBMS_MVIEW.EXPLAIN_MVIEW has to say about MV2.
select my_mv_capabilities( 'MV2', 'REFRESH' ) as mv_report from dual ;
MV_REPORT -------------------------------------------------------------------------------- Capable of: REFRESH_COMPLETE REFRESH_FAST REFRESH_FAST_AFTER_INSERT Not Capable of: REFRESH_FAST_AFTER_ONETAB_DML AMT_SUM SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ONETAB_DML COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ANY_DML see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
The report tells us MV2 is fast refreshable after insert, as we saw earlier, but not after other types of DML. This is how to recognize an insert-only materialized view.
So the lesson here is do not assume materialized views created with REFRESH FAST ON COMMIT will always refresh fast on commit. Always check it with DBMS_MVEW.EXPLAIN_MVIEW to see whether or not it is an "insert-only" materialized view.
Cleanup
drop materialized view mv ; drop materialized view mv2 ; drop materialized view log on t2 ; delete t2 ; insert into t2 select * from t2_backup ; commit;