Materialized Views

Views vs Materialized Views

Like its predecessor the view, materialized views allow you to store the definition of a query in the database.

Table View Materialized View





select * from T ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create view v
as
select *
from   t ;

select * from V ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create materialized view mv
as
select *
from   t ;

select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 

Unlike views, however, materialized views also store the results of the query in the database. In the following queries note how the rowid's for the table and the view are identical, indicating the view returns the exact same data stored in the table. The rowids of the materialized view, on the other hand, differ from those of the table. This indicates the materialized view is returning a physically separate copy of the table data.

Table View Materialized View
select rowid
from T
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from V
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from MV
order by rowid ;

ROWID
------------------
AAAgZFAAEAAADyEAAA
AAAgZFAAEAAADyEAAB
AAAgZFAAEAAADyEAAC
AAAgZFAAEAAADyEAAD
 

The difference between views and materialized views becomes even more evident than this when table data is updated.

Table View Materialized View
update t set val = upper(val);
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 

Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. Refreshing can either be done manually, as below, or automatically by Oracle in some cases.

Table View Materialized View
execute dbms_mview.refresh( 'MV' );
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 

Now that the materialized view has been refreshed its data matches that of its base table.

Cleanup

drop materialized view mv ;

drop view v ;

update t set val = lower(val);
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-12874.html]SQL Snippets: Materialized Views - Views vs Materialized Views[/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-12874.html">SQL Snippets: Materialized Views - Views vs Materialized Views</a>

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

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