Creating Materialized Tables¶

Syntax of the statement CREATE MATERIALIZED TABLE.

CREATE [ OR REPLACE ] MATERIALIZED TABLE [<database:identifier>.]<name:identifier>
  (
      <VDP type field> [, <VDP type field> ]*
    | <SQL type field> [, <SQL type field> ]*
  )
  [ FOLDER = <literal> ]
  [ <primary key> ]

CREATE [ OR REPLACE ] MATERIALIZED TABLE [<database:identifier>.]<name:identifier>
  AS <SELECT statement>

<VDP type field> ::=
  <name:identifier> : <Virtual DataPort data type>
    [ ( <property> [, <property> ]+ ) ]

<SQL type field> ::=
  <name:identifier> <SQL type> [ ( <property> [, <property> ]+ ) ]

<SQL type> ::=
    BIT ( <integer> )
  | BIT VARYING ( <integer> )
  | BLOB
  | BOOLEAN
  | CHAR ( <integer> )
  | CHARACTER ( <integer> )
  | CHARACTER VARYING ( <integer> )
  | DATE
  | DECIMAL
  | DECIMAL ( <integer>, <integer> )
  | DOUBLE
  | DOUBLE PRECISION
  | FLOAT
  | INT
  | INTEGER
  | LONG
  | NCHAR ( <integer> )
  | NUMERIC
  | NUMERIC ( <integer> , <integer> )
  | NVARCHAR ( <integer> )
  | REAL
  | SMALLINT
  | TEXT
  | TIMESTAMP
  | TIMESTAMP WITH TIME ZONE
  | VARCHAR ( <integer> )

<property> ::=
  <name:identifier> [ = <value:identifier> ]

<primary key> ::=
  [ CONSTRAINT <name:literal> ]
  PRIMARY KEY ( <field name:literal> [, <field name:literal> ]* )

<Virtual DataPort data type> ::= (see Virtual DataPort data types)

There are three ways of creating a materialized view:

Syntax 1: CREATE MATERIALIZED TABLE

-- Using ANSI SQL data types
CREATE OR REPLACE MATERIALIZED TABLE united_states_of_america_state (
        name VARCHAR(20)
      , abbreviation CHAR(2)
      , capital VARCHAR(100)
   )
CONSTRAINT 'primary_key_usa_state' PRIMARY KEY ('name');
;

-- Using Virtual DataPort data types
CREATE OR REPLACE MATERIALIZED TABLE united_states_of_america_state (
         name : text
       , abbreviation : text
       , capital : text
    )
CONSTRAINT 'primary_key_usa_state' PRIMARY KEY ('name');
;

These statements create a materialized table without data in it. The section below explains how to insert data in it.

Both statements do the same thing:

• The first one uses the ANSI SQL syntax, including the names of the data types.

• The second one uses the Virtual DataPort data types. Note that in this syntax, there is a colon after the name of the type and in the first syntax, there is not.

One is not better than the other; when creating the table with ANSI SQL data types, these get internally converted to the Virtual DataPort data types.

The primary key field of this table is “name”. Note that Virtual DataPort does not enforce the primary keys of views. You have to make sure you do not insert repeated values for this field.

Syntax 2: CREATE MATERIALIZED TABLE … AS SELECT …

CREATE MATERIALIZED TABLE order_current_year
AS SELECT *
FROM order
WHERE TRUNC(sale_date, 'YEAR') = TRUNC(CURRENT_DATE, 'YEAR');

This statement creates a materialized view with the schema of the SELECT statement and inserts the result of the query in it.

Syntax 3: SELECT … INTO …

SELECT <expression> [, <expression>]*
  INTO <table name:identifier>
  FROM <FROM clause>
  [ <WHERE clause> ]
  [ <GROUP BY clause> ]
  [ <HAVING clause> ]

This statement fails if the view <table name> already exists.

For example:

SELECT order_date, total_amount
INTO order_current_year
FROM order
WHERE TRUNC(sale_date, 'YEAR') = TRUNC(CURRENT_DATE, 'YEAR');

This statement creates the materialized table “order_current_year” table with the fields “order_date” and “total_amount” and inserts the results of the query in it.

When creating a materialized table, the Server switches the database to single user mode until the operation finishes. With the first syntax (CREATE MATERIALIZED TABLE ...), the operation finishes instantly.

With the second syntax (CREATE MATERIALIZED TABLE ... AS SELECT ...) and the third one (SELECT ... INTO ...), the database stays in single user mode until the query finishes and its result is stored. If the query is long, it is better to execute CREATE MATERIALIZED TABLE, which finishes instantly; and then, insert the data into the materialized table with the command INSERT INTO <materialized table> ( <SELECT clause> ) (see the section below). As the INSERT does not switch the database to single user mode, inserting the data with the INSERT allows for a higher level of concurrency.

Inserting Data into Materialized Tables¶

There are two ways of inserting data into a materialized view:

Syntax 1

INSERT INTO <materialized table> ( <fields> )
VALUES (<values> ) [, ( <values> ) ]*

Inserts one or more rows into a materialized table.

This statement supports the same options as the one available for base and derived views (see Syntax of the INSERT statement) with the addition than you can insert several rows within the same statement. For example:

INSERT INTO usa_state (name, abbreviation, capital) VALUES
  ('Arizona', 'AZ', 'Phoenix')
, ('California', 'CA', 'Sacramento')
, ('New York', 'NY', 'Albany')
, ('North Dakota', 'ND', 'Bismarck');

Syntax 2

INSERT INTO <materialized table> ( <SELECT clause> )

Inserts the result of a SELECT clause into a materialized table. The benefit over the previous syntax is that with this one, you can insert several rows at once.

The result of the query has to have the same schema as the materialized table and in the result, the fields must be in the same order as in the target table.

INSERT INTO usa_state (
  SELECT 'Arizona', 'AZ', 'Phoenix'
);