View a markdown version of this page

CREATE INDEX - Amazon Aurora DSQL
Supported syntaxDescriptionParametersExamples

CREATE INDEX

CREATE INDEX defines a new index. In Aurora DSQL, index creation is always asynchronous, so you must specify the ASYNC keyword. For information about how Aurora DSQL runs asynchronous index builds and how to monitor them, see Asynchronous indexes in Aurora DSQL.

Supported syntax

CREATE [ UNIQUE ] INDEX ASYNC [ [ IF NOT EXISTS ] name ] ON table_name
    ( { column_name | ( expression ) } [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WHERE predicate ]

Description

CREATE INDEX constructs an index on the specified columns of the specified table. Indexes are primarily used to enhance database performance, though inappropriate use can result in slower performance.

You specify the key fields for the index as column names, or alternatively as expressions written in parentheses. You can specify multiple fields to create a multicolumn index.

An index field can be an expression computed from the values of one or more columns of the table row. Use this feature to obtain fast access to data based on some transformation of the basic data. For example, you can create an index on upper(col), so a query with the condition WHERE upper(col) = 'JIM' can use that index.

All functions and operators used in an index definition must be immutable. That is, their results must depend only on their arguments and never on any outside influence, such as the contents of another table or the current time. This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index expression, remember to mark the function IMMUTABLE when you create it. If you change the definition of the user-defined function used by an index expression, be sure to drop and rebuilt the index.

The optional WHERE clause defines a partial index. A partial index contains entries for only the rows of the table that satisfy the predicate, rather than every row. When queries frequently target a well-defined subset of a table's rows, you can improve performance by creating an index on only that portion of the table. For example, a table might contain both active and archived records. If queries usually access only the active ones, you can index only the active rows.

Parameters

UNIQUE

Causes the system to check for duplicate values in the table when it creates the index, if data already exists, and each time you add data. Attempts to insert or update data that would result in duplicate entries generate an error.

IF NOT EXISTS

Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing index resembles the one that would have been created. The index name is required when IF NOT EXISTS is specified.

INCLUDE

The optional INCLUDE clause specifies a list of columns to include in the index as non-key columns. You can't use a non-key column in an index scan search qualification, and Aurora DSQL disregards it for purposes of any uniqueness or exclusion constraint that the index enforces. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, because they are available directly from the index entry. Adding non-key columns therefore allows index-only scans for queries that otherwise couldn't use them.

Expressions aren't supported as included columns, because they can't be used in index-only scans.

name

The name of the index to create. You can't include a schema name here. Aurora DSQL always creates the index in the same schema as its parent table. The name of the index must be distinct from the name of any other relation, such as a table, sequence, index, or view, in that schema. If you omit the name, Aurora DSQL chooses a suitable name based on the parent table's name and the indexed column names.

table_name

The name, optionally schema-qualified, of the table to index.

column_name

The name of a column of the table.

expression

An expression based on one or more columns of the table. You usually must write the expression with surrounding parentheses, as shown in the syntax. However, you can omit the parentheses if the expression has the form of a function call.

NULLS FIRST

Specifies that nulls sort before non-nulls.

NULLS LAST

Specifies that nulls sort after non-nulls.

NULLS DISTINCT
NULLS NOT DISTINCT

Specifies whether null values are considered distinct, that is, not equal, for a unique index. The default is that they are distinct, so that a unique index can contain multiple null values in a column.

WHERE predicate

The optional WHERE clause specifies a Boolean expression, or predicate, that defines a partial index. The index includes only rows for which the predicate evaluates to true. The predicate can refer to any column of the table, not only the columns being indexed. As with index expressions, the predicate must contain only immutable functions, operators, and column references.

Aurora DSQL can use a partial index for a query only when it can prove that the query's WHERE conditions imply the index's predicate. If it can't, Aurora DSQL doesn't use the index for that query.

Examples

To create a unique index on the column title in the table films.

CREATE UNIQUE INDEX ASYNC title_idx ON films (title);

To create a unique index on the column title with included columns director and rating in the table films.

CREATE UNIQUE INDEX ASYNC title_idx ON films (title) INCLUDE (director, rating);

To create an index on the expression lower(title), which allows efficient case-insensitive searches.

CREATE INDEX ASYNC ON films ((lower(title)));

This example omits the index name, so Aurora DSQL chooses a name, typically films_lower_idx.

To create an index with non-default sort ordering of nulls.

CREATE INDEX ASYNC title_idx_nulls_low ON films (title NULLS FIRST);

To create a partial index on title that indexes only the rows where rating is greater than 5:

CREATE INDEX ASYNC high_rating_idx ON films (title) WHERE rating > 5;