Section courante

A propos

Section administrative du site

CREATE MATERIALIZED VIEW / SHAPSHOT

Créer une vue matériel
Oracle Database SQL  

Syntaxe

CREATE MATERIALIZED VIEW [schema.]mview
      [
             ORGANIZATION {HEAP [
                         PCTFREE int
                           PCTUSED int
                           INITRANS int
                           MAXTRANS int
                           STORAGE storage_clause
                           TABLESPACE tablespace
                 ] [COMPRESS int|NOCOMPRESS]
         | INDEX [
                         [
                                PCTFREE int
                                   PCTUSED int
                                   INITRANS int
                                   MAXTRANS int
                                   STORAGE storage_clause
                                   TABLESPACE tablespace
                        ]
                           {MAPPING TABLE | NOMAPPING}
                           [PCTTHRESHOLD int]
                           [COMPRESS int|NOCOMPRESS]
                           [ [INCLUDING column_name] OVERFLOW [
                                PCTFREE int
                                 PCTUSED int
                                   INITRANS int
                                   MAXTRANS int
                                   STORAGE storage_clause
                                   TABLESPACE tablespace
                        ] ]
                 ]
         | EXTERNAL [
                         ([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}]
                 ] }
          ]
         [USING INDEX [
                         PCTFREE int
                           PCTUSED int
                           INITRANS int
                           MAXTRANS int
                           STORAGE storage_clause
                           TABLESPACE tablespace
                 ]]
            [{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 [
                         [[
                                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} ]
                        ]
                      [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}]] |
                        [CLUSTER cluster (column,...) [Partitioning clause]
                      [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]] |
                        [PCTFREE int
                           PCTUSED int
                           INITRANS int
                           MAXTRANS int
                           STORAGE storage_clause
                           TABLESPACE tablespace]
                 ]]
            [{REFRESH [[
                                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
                        ]] | NEVER REFRESH]
               [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
                  AS subbquery;

Description

Cette instruction permet d'effectuer la création d'une vue matérialisé.

Voir également

Article - Les géants de l'informatique - Oracle

Dernière mise à jour : Lundi, le 11 mai 2015