CREATE INDEX

Syntax

CREATE INDEX
    index_name
ON
    materialized_view_name 
(
    column_name [, ...]
);

Description

This statement creates an index on the specified column(s) of an existing Materialized View. Indexes, when created appropriately, are generally used to improve queries performance. The decision to create an index on a set of columns depends on various factors and should be made based on careful consideration of specific queries, a user normally runs on a Materialized View. Once created, an index is updated after each change in the Materialized View's rows. Moreover, given that an index is a separate relation, its size grows proportional to the size of the Materialized View, it is created on. Therefore, creating an index has an impact on the insertion performance and total storage size of the Materialized View. Primary key column(s) set (if any) are indexed automatically.

Examples

Assume you create a stream named pageviews along with a materialized view named pagevisits by the following statements. See CREATE MATERIALIZED VIEW AS for more details on creating materialized views.

CREATE STREAM pageviews (
  viewtime BIGINT,
  userid VARCHAR,
  pageid VARCHAR
) WITH (
  'topic' = 'pageviews',
  'value.format' = 'json'
);

CREATE MATERIALIZED VIEW
  pagevisits
AS SELECT
  userid, pageid
FROM pageviews
WHERE userid <> 'User_5';

The following statement creates an index named visits_idx on pagevisits using the userid column. This index improves the performance of queries that retrieve rows by filtering on userid values.

CREATE INDEX 
  visits_idx 
ON 
  pagevisits (userid);

You can also create an index on multiple columns from a materialized view. For example, the statement below creates an index on pagevisits using userid and pageid columns.

Note The order of columns in an index matters.

CREATE INDEX 
  visits_compound_idx 
ON 
  pagevisits (userid, pageid);