CREATE INDEX
Syntax
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.
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.
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.
Last updated