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;