CREATE INDEX¶

Creates a new secondary index in an existing hybrid table and populates the index with data.

The creation of an index is an online (non-blocking) operation. The hybrid table will remain available for SELECT and DML statements while the index is being built. However, if the hybrid table is not in active use and downtime is not an issue, it is recommended that you recreate the hybrid table with indexes defined. See also Create a hybrid table.

See also:

DROP INDEX , SHOW INDEXES , CREATE HYBRID TABLE , DROP TABLE , DESCRIBE TABLE , SHOW HYBRID TABLES

Syntax¶

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

Parameters¶

index_name

Specifies the identifier for the new index. You must specify a unique name for each new index on a given hybrid table. No other secondary index with either the same name or 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 in the hybrid table. All the requirements for index columns defined at table creation apply to column identifiers.

A hybrid table cannot contain two secondary indexes defined on the same ordered set of columns.

Columns with semi-structured data types (VARIANT, OBJECT, ARRAY) or geospatial data types (GEOGRAPHY, GEOMETRY) are not supported in secondary indexes.

Access control requirements¶

To create an index, you must use a role that has OWNERSHIP privilege on the hybrid table.

Usage notes¶

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

  • 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 can run at any time.

  • You can track the progress of an index build 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 recreate 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, first stop the index build by dropping the index, then drop the column. If you try to drop the column 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 builds do 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 began executing after the CREATE INDEX command was complete, 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 build process concludes successfully and the status of the index is ACTIVE.

Examples¶

To run the following CREATE INDEX example, first create and load the hybrid table.

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);
Copy

Now you can create an index on the table.

CREATE OR REPLACE INDEX vidx ON mytable (val);
Copy
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

If a failure occurs while the index is being built, the SHOW INDEXES command reports the following status:

BUILD FAILURE Index build failed. Please drop the index and re-create it.

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

DROP INDEX mytable.vidx;
Copy
+-------------------------------------+
| status                              |
|-------------------------------------|
| Statement executed successfully.    |
+-------------------------------------+
Language: English