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.
No comments:
Post a Comment