Materialized Views

Join Queries

So far in this tutorial we have only seen materialized views based on a single table. Materialized views can also be created on multi-table queries to store the pre-calculated results of expensive join operations. Here is a simple example.

select * from t ;
 
       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
select * from t2 ;
 
       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  ,
    t.val    t_val  ,
    t2.key   t2_key ,
    t2.amt   t2_amt
  from
    t, t2
  where
    t.key = t2.t_key
;

select t_key, t_val, t2_key, t2_amt
from   mv ;
 
     T_KEY T_VAL     T2_KEY     T2_AMT
---------- ----- ---------- ----------
         1 a             10        100
         1 a             20        300
         1 a             30        200
         2 b             40        250
         2 b             50        150
 

REFRESH FAST

For a materialized view with only joins (no aggregates, unions, subqueries, etc.) to be fast refreshable certain restrictions beyond the General Restrictions on Fast Refresh must be met. These additional restrictions are:

  • materialized view logs with rowids must exist for all of the defining query's base tables
  • the SELECT clause cannot contain object type columns
  • the defining query cannot have a GROUP BY clause or aggregates
  • rowid columns for each table instance in the FROM clause must appear in the SELECT clause.
    -- from Restrictions on Fast Refresh on Materialized Views with Joins Only

In addition to these restrictions there are some recommended practices for using join queries. They are as follows.

"If a materialized view contains joins but no aggregates, then having an index on each of the join column rowids in the detail table will enhance refresh performance greatly, because this type of materialized view tends to be much larger than materialized views containing aggregates."
-- from Refreshing Materialized Views: Tips for Refreshing Materialized Views Without 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.

The Prototype

Applying these restrictions and recommendations to our test case above yields the following prototypical materialized view with joins. 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 t with rowid, sequence ;

create materialized view log on t2 with rowid, sequence ;

create materialized view mv
  refresh fast on commit
  enable query rewrite
  as
    select
      t.key    t_key     ,
      t.val    t_val     ,
      t2.key   t2_key    ,
      t2.amt   t2_amt    ,
      t.rowid  t_row_id  ,
      t2.rowid t2_row_id
    from
      t, t2
    where
      t.key = t2.t_key
;

create index mv_i1 on mv ( t_row_id ) ;
create index mv_i2 on mv ( t2_row_id ) ;

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 t_key, t_val, t2_key, t2_amt
from   mv ;
 
     T_KEY T_VAL     T2_KEY     T2_AMT
---------- ----- ---------- ----------
         1 a             10        100
         1 a             20        300
         1 a             30        200
         2 b             40        250
         2 b             50        150
 

Now let's do some DML on both base tables and see the effect on MV.

insert into t2 values ( 60, 3, 300 ) ;

update t set val = upper(val) ;

commit;

select t_key, t_val, t2_key, t2_amt
from   mv ;
 
     T_KEY T_VAL     T2_KEY     T2_AMT
---------- ----- ---------- ----------
         1 A             10        100
         1 A             20        300
         1 A             30        200
         2 B             40        250
         2 B             50        150
         3 C             60        300
 

Both changes are reflected in MV, as expected.

Query Rewrite

Materialized views containing joins can be used by the query rewrite facility (see 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 - ANSI Join Syntax

When we attempt to create a materialized view with the ANSI join syntax equivalent of the defining query used above we are surprisingly rewarded with an ORA error.

create materialized view mv2
  refresh fast
  as
    select
      t.key    t_key     ,
      t.val    t_val     ,
      t2.key   t2_key    ,
      t2.amt   t2_amt    ,
      t.rowid  t_row_id  ,
      t2.rowid t2_row_id
    from
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
;
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
      *
ERROR at line 12:
ORA-12015: cannot create a fast refresh materialized view from a complex query


 

While this behaviour appears to be a bug at first glance, Metalink note 420856.1 explains that it is really an undocumented limitation of fast refresh materialized views.

An examination of the EXPLAIN_MVIEW results for this case points to some behind-the-scenes transformations with ANSI syntax which may be causing the limitation.

select
  my_mv_capabilities
  ( 'create materialized view mv2
    refresh fast
    as
      select
        t.key    t_key     ,
        t.val    t_val     ,
        t2.key   t2_key    ,
        t2.amt   t2_amt    ,
        t.rowid  t_row_id  ,
        t2.rowid t2_row_id
      from
        T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )'
  , 'REFRESH_FAST_AFTER_INSERT'
  ) as mv_report
from dual ;
 
MV_REPORT
--------------------------------------------------------------------------------

Not Capable of:

  REFRESH_FAST_AFTER_INSERT
    inline view or subquery in FROM list not supported for this type MV

  REFRESH_FAST_AFTER_INSERT
    inline view or subquery in FROM list not supported for this type MV

  REFRESH_FAST_AFTER_INSERT
    view or subquery in from list
 

Cleanup

drop materialized view mv ;

drop materialized view log on t ;
drop materialized view log on t2 ;

delete t2 ;
delete t ;
insert into t select * from t_backup ;
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-12922.html]SQL Snippets: Materialized Views - Join 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-12922.html">SQL Snippets: Materialized Views - Join Queries</a>

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

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