Materialized Views

Aggregate Queries

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.

Materialized Views with Aggregates: Requirements for Refresh Fast After Any DML
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;
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12924.html]SQL Snippets: Materialized Views - Aggregate Queries[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12924.html">SQL Snippets: Materialized Views - Aggregate Queries</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - Aggregate Queries
  • URL (href): http://www.sqlsnippets.com/en/topic-12924.html