CREATE MATERIALIZED VIEW

Create a materialized view. - The word "snapshot" is synonymous with "materialized view".

Syntax:

   CREATE MATERIALIZED VIEW [schema.]materialized_view options
      [USING INDEX index_options]
         [REFRESH [refresh_options]]
            [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]

   CREATE MATERIALIZED VIEW [schema.]materialized_view
      ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION]
         [USING INDEX index_options]
            [REFRESH [refresh_options]]
               [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]

options:

   CLUSTER cluster (column,...) [Partitioning clause]
      [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]
or
   storage_options [LOB/Modify LOB Storage clause] [CACHE | NOCACHE]
      [Partitioning clause] [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]

   Where storage_options can be any of:
   PCTFREE int
   PCTUSED int
   INITRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   LOGGING | NOLOGGING

index_options
   INITRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace

refresh_options:
   FAST | COMPLETE | FORCE
   ON [DEMAND | COMMIT]
   {NEXT | START WITH} date
   WITH {PRIMARY KEY | rowid}
   USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT
   USING {MASTER|LOCAL} ROLLBACK SEGMENT rb_segment

Related Commands:

DIMENSION - CREATE DIMENSION
GRANT ALTER ANY MATERIALIZED VIEW
MVIEW - ALTER MATERIALIZED VIEW
MVIEW - DROP MATERIALIZED VIEW

Related Views:

 DBA_MVIEWS           ALL_MVIEWS           USER_MVIEWS 
 DBA_MVIEW_AGGREGATES ALL_MVIEW_AGGREGATES USER_MVIEW_AGGREGATES 
 DBA_MVIEW_ANALYSIS   ALL_MVIEW_ANALYSIS   USER_MVIEW_ANALYSIS 
 DBA_MVIEW_DETAIL_RELATIONS ALL_MVIEW_DETAIL_RELATIONS USER_MVIEW_DETAIL_RELATIONS 
 DBA_MVIEW_JOINS      ALL_MVIEW_JOINS      USER_MVIEW_JOINS 
 DBA_MVIEW_KEYS       ALL_MVIEW_KEYS       USER_MVIEW_KEYS 

 DBA_SNAPSHOTS               ALL_SNAPSHOTS               USER_SNAPSHOTS
 DBA_SNAPSHOT_LOGS           ALL_SNAPSHOT_LOGS           USER_SNAPSHOT_LOGS
 DBA_SNAPSHOT_LOG_FILTER_COLS
 DBA_SNAPSHOT_REFRESH_TIMES  ALL_SNAPSHOT_REFRESH_TIMES  USER_SNAPSHOT_REFRESH_TIMES  




Back to the Top

Simon Sheppard
SS64.com