SQL Snippets ™: Tutorials for Oracle Developers

Materialized views can be useful for pre-calculating and storing derived values such as AMT_MAX in the following snippet.

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

create materialized view mv
  refresh fast on commit
  as
    select t_key, MAX( AMT ) AMT_MAX
    from t2
    group by t_key
;

 

Such materialized views make queries like this

select t_key, amt_max
FROM MV
order by t_key ;
 

     T_KEY    AMT_MAX
---------- ----------
         1        300
         2        250
 

faster than its equivalent query.

select t_key, max( amt ) as amt_max
FROM T2
group by t_key
order by t_key ;
 

     T_KEY    AMT_MAX
---------- ----------
         1        300
         2        250
 

Wouldn't it be nice if Oracle could use the information in MV to resolve this last query too? If your database has a feature called Query Rewrite available and enabled this happens automatically. To see it in action we first need to make the materialized view available to Query Rewrite like this.

alter materialized view mv ENABLE QUERY REWRITE ;
 

(See Gotcha - ORA-00439 below if you encounter an ORA-00439 error at this step.)

Note that materialized views which do not include the ENABLE QUERY REWRITE clause will have Query Rewrite disabled by default.

Next we collect statistics on the materialized view to help Oracle optimize the query rewrite process.

execute dbms_stats.gather_table_stats( user, 'MV' ) ;
 

Finally we can confirm Oracle will use the materialized view in queries by turning SQL*Plus's Autotrace feature on.

set autotrace on explain
set linesize 95

select t_key, max( amt ) as amt_max
FROM T2
group by t_key
order by t_key ;

     T_KEY    AMT_MAX
---------- ----------
         1        300
         2        250

Execution Plan
----------------------------------------------------------
Plan hash value: 446852971

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     2 |    14 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                |      |     2 |    14 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MV   |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

 

Note how the optimizer chose to access MV for its pre-calculated MAX(AMT) values in line 2 even though the query itself made no mention of MV. Without the Query Rewrite feature the execution plan would look like this.

alter session set QUERY_REWRITE_ENABLED = FALSE ;

select t_key, max( amt ) as amt_max
FROM T2
group by t_key
order by t_key ;

     T_KEY    AMT_MAX
---------- ----------
         1        300
         2        250

Execution Plan
----------------------------------------------------------
Plan hash value: 50962384

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   130 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     5 |   130 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     5 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

 

Note how the optimizer chose to access T2 this time. Each time this query is executed it has to re-calculate MAX(VAL) from the information in T2 for each group, a more expensive approach than simply selecting pre-calculated column values from MV is.

Gotcha - ORA-00439

The materialized view query rewrite feature is not available in Oracle XE and some other Oracle configurations. If you attempt to use ENABLE QUERY REWITE in an Oracle database where the feature is not enabled you will receive an ORA-00439 error.

create materialized view mv2
  refresh fast on commit
  ENABLE QUERY REWRITE
  as
    select
      t_key ,
      count(*)   as row_count ,
      count(amt) as amt_count
    from t2
    group by t_key
;
    from t2
         *
ERROR at line 9:
ORA-00439: feature not enabled: Materialized view rewrite
 

Cleanup

alter session set query_rewrite_enabled = true ;

set autotrace off

drop materialized view mv ;

drop materialized view log on t2 ;