Materialized Views : Refreshing a materialized view | Subtopic

• refresh type and refresh interval can be specified while creating.
• fast, complete, never or force
• manual refresh >> use DBMS_MVIEW.REFRESH

• EXECUTE DBMS_MVIEW.REFRESH('view_name','p')
• c-complete; f- fast; p- fast using pct; ? - force




•  DBMS_MVIEW.REFRESH_ALL_MVIEWS; refresh all mviews one by one.

• ALTER MATERIALIZED VIEW mviewname; 
• DROP MATERIALIZED VIEW mviewname;

Materialized Views : Privileges Required | Types

• We need spl privileges to be able to create mat. views.
• must have CREATE MATERIALIZED VIEW privilege.
• and the CREATE TABLE or CREATE ANY TABLE privilege.
• suffucient allocated quota in the local tablespace.
• If accessing tables in other tablespaces or in remote databases; must have access to that tablespace and CREATE DATABASE LINK system privilege.
• QUERY REWRITE or GLOBAL QUERY REWRITE privilege.

GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;

• types

• read only and updatable; read only can't pass the data back to its parent table. while updatable can.

• there is more control on mat view refresh in case of read only; but there is very less control if updatable mat. view is created.

• read only mat view is created using
  CREATE TABLE AS SELECT
  command.

• As soon as the mat. view is created, its underlying table is populated with data.

• TYPES
• there is another way of distinguishing the various types.
• 1) which contain just a replica of the data
• 2) which aggregate the data before storing in the mat. table.
• 3) which are based on other mat. views.

Materialized views were previously known as SNAPSHOTS.

 The common syntax for mat view is :

CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

• refresh - FAST(using materialized view logs) or COMPLETE(truncate, rerun query, repopulate) or FORCE(try fast otherwise complete).

• index on a materialized view - oracle automatically does not create any index on the local table of the mview except on the PK. Rest all users have to create themselves.
•  
•  
•  
•  
• DBMS_ADVICER
• generates recommendations for creation and indexing of mviews.
• 
  

Author's Advice