Thursday, February 21, 2013

Creating a Complete Refreshable Materialized View

More on Materialized Views

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

Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.

How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...