30 June, 2015

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