CREATE INDEX¶

Syntax¶

CREATE [ OR REPLACE ] INDEX [ IF NOT EXISTS ] <index_name>
  ( <col_name>
      [ , <col_name> ] ) ON <table_name>

Parameters¶

index_name

Specifies the identifier for the new index. The name must be unique for the hybrid table for which the index is created. No other secondary index with either the same name or on the same ordered set of columns can exist on the hybrid table.

table_name

Specifies the name of an existing hybrid table that will hold the new index.

col_name

Specifies the name of an existing column of the hybrid table. All the requirements for index columns defined at table creation apply to column identifiers.

Usage Notes¶

• The CREATE INDEX command cannot be used to add a foreign, primary, or unique key constraint.

• In order to create an index, you must be using a role that has the OWNERSHIP privilege on the hybrid table.

• You must choose a unique name for each new index for a given hybrid table.

• When picking the columns for the new index, note that a hybrid table cannot contain two secondary indexes defined over the same ordered set of columns.

• Some column types (for example, VARIANT) are not allowed in a secondary index.

• The creation of a new index does not concurrently block other workloads. The hybrid table is available for concurrent SELECT and DML statements.

• Only one active index build operation per hybrid table is permitted at any time.

• You can track the progress of an index creation by using SHOW INDEXES. The STATUS column can take the following values:

  • ACTIVE: Index is complete and can be used to retrieve data.

  • SUSPENDED: Index is only updated and is not used to retrieve data.

  • BUILD FAILURE: An error has occurred with the index build process. You need to drop and re-create the index.

  • BUILD IN PROGRESS: Index is being built and is not used to retrieve data.

• You can rebuild a non-active index, where the status is SUSPENDED, BUILD FAILURE or BUILD IN PROGRESS, by using DROP INDEX and CREATE INDEX.

• If you want to drop a column that is part of an index that is being built, you will first need to stop the index build by dropping the index and then dropping the column. If you try to drop the column first before dropping the index, you will receive this error message: Column '<col_name>' cannot be dropped because it is used by index '<index-name>'.

• Online index build will not make progress until all the active transactions with DMLs on the same table at the time when the CREATE INDEX statement was issued are completed. If any of those transactions remain idle for more than 5 minutes, they will abort by default. See Transactions.

• During the index build process, any DML performs its writes to the new index, but does not use the index to retrieve data.

• A small number of concurrent DMLs, which initiated execution after the CREATE INDEX command has completed, may fail and return this error: DML was unaware of concurrent DDL. Please retry this query.

• If the aborted DML statements belong to a multi-statement transaction, the transaction will roll-back only if the TRANSACTION_ABORT_ON_ERROR parameter is set to TRUE.

• A newly-created index will be used for retrieving data only when the index creation process concludes successfully and the status of the index is ACTIVE.

Examples¶

The following examples presumes that you have already created a hybrid table and that it contains some records.

CREATE OR REPLACE HYBRID TABLE mytable (
  pk INT PRIMARY KEY,
  val INT
  val2 INT
);

INSERT INTO mytable SELECT seq, seq+100, seq+200
  FROM (SELECT seq8() seq FROM TABLE(GENERATOR(rowcount => 100)) v);

You can create an index on the table using the following:

CREATE OR REPLACE INDEX vidx (val) ON mytable;

The command returns this message: BUILD IN PROGRESS The index is being built and will register concurrent DML changes to the base table. The index will not be used until it has finished building.

If a failure occurs while the index build is in progress, the SHOW INDEXES command will indicate that the operation has failed: BUILD FAILURE Index build failed. Please drop the index and re-create it.

If you decide to stop the index build, then you can use a DROP INDEX command:

DROP INDEX vid ON mytable;

+-------------------------------------+
| status                              |
|-------------------------------------|
| Statement executed successfully.    |
+-------------------------------------+