CREATE MATERIALIZED VIEW

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

Syntax:

   CREATE MATERIALIZED VIEW [schema.]mview
      Mview_Options
         [USING INDEX storage_options]
            [{REFRESH [refresh_options] | NEVER REFRESH]
               [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
                  AS subbquery;

   CREATE MATERIALIZED VIEW [schema.]mview
      ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION]
         [USING INDEX storage_options]
            [{REFRESH [refresh_options] | NEVER REFRESH]
               [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
                  AS subbquery;

Mview_Options:

   ORGANIZATION {HEAP [storage_options] [COMPRESS int|NOCOMPRESS]
                | INDEX idx_organized_tbl_clause
                | EXTERNAL external_table_clause }
or
   storage_options 
      [nested_storage_clause]
      [LOB/Modify LOB Storage clause] [lob_partition_storage]
      [varray_clause] [lob_partition_storage]
          [COMPRESS int|NOCOMPRESS]
             [Partitioning clause] [LOGGING | NOLOGGING]
                [CACHE | NOCACHE][PARALLEL int | NOPARALLEL]
                   [BUILD {IMMEDIATE|DEFERRED}]
or
   CLUSTER cluster (column,…) [Partitioning clause]
      [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]

storage_options:
   PCTFREE int
   PCTUSED int
   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

idx_organized_tbl_clause:
   storage_option(s) 
   {MAPPING TABLE | NOMAPPING}
   [PCTTHRESHOLD int]
   [COMPRESS int|NOCOMPRESS]
   [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

external_table_clause:
   ([TYPE access_driver_type]
      DEFAULT DIRECTORY directory [ACCESS PARAMETERS {USING CLOB subquery | (opaque_format_spec) }]
         LOCATION (directory:'location_specifier' [,directory2:'location_specifier2'…)
         ) [REJECT LIMIT {int|UNLIMITED}]

nested_storage_clause:
   NESTED TABLE {nested_item | COLUMN_VALUE}
      [ [ELEMENT] IS OF TYPE (ONLY type) ]] |  [ [NOT] SUBSTITUTABLE AT ALL LEVELS ]] 
      STORE AS storage_table 
         [RETURN AS {LOCATOR|VALUE} ]

Missing from this page are the options for SCOPE REF Constraints (OBJECT Tables) - see the Oracle docs for this.

Related Commands:

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

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_MVIEW_LOGS             ALL_MVIEW_LOGS       USER_MVIEW_LOGS
 DBA_MVIEW_LOG_FILTER_COLS
 DBA_MVIEW_REFRESH_TIMES    ALL_MVIEW_REFRESH_TIMES USER_MVIEW_REFRESH_TIMES


© Copyright SS64.com 1999-2014
Some rights reserved