Materialized Views
What is a Materialized View?
Creating a Complete Refreshable Materialized View
Creating a Fast Refreshable Materialized View
Creating a Fast-Refreshable Materialized View based on a Complex Query
Creating a Complete Refreshable Materialized View
Before you can create a Materialized View you need CREATE MATERIALIZED VIEW and CREATE TABLE system privileges.
Appropriate for Materialized View’s where the base tables have a significant amount of row changes. Also required when Fast Refresh is not possible.
create materialized view EXPERIMENTS_MV
refresh
complete
on demand
as
select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES,
count(*) AS TOTAL
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
group by USERNAME,EXP_NAME
order by USERNAME,EXP_NAME;
Query user_mviews
select mview_name
,refresh_method
,refresh_mode
,fast_refreshable
from user_mviews
where mview_name = ‘EXPERIMENTS_MV’;
MVIEW_NAME REFRESH_METHOD REFRESH_MODE FAST_REFRESHABLE
--------------- ------------- ----------- ----------------
EXPERIMENTS_MV COMPLETE DEMAND NO
How to refresh a Materialized View:
Use the refresh procedure of the DBMS_MVIEWpackage.
Passing in one parameter:
(The refresh method will be the default refresh method for
that Materialized View)
The name of the Materialized View and the refresh method.
SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV');
Passing in two parameters:
The name of the Materialized View
The refresh method.
SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','C');
You can include more than one Materialized View name
with a comma delimited string in quotes.
For example:
SQL> exec dbms_mview.refresh('MV_1, MV_2,MV_3’,'CFC');
If you exclude the refresh type parameter then the default
refresh method is used. You will get an error if you try to
use F for Fast Refresh and your Materialized View is not
set up as a Fast Refreshable Materialized View.
Refresh Types:
C = Complete
F = Fast
? = Force
SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','?');
Force: Performs a FAST if possible, otherwise it performs a
No comments:
Post a Comment