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 remains available for SELECT and DML statements while the index is being built. However, if the hybrid table isn’t in active use and downtime isn’t an issue, Snowflake recommends that you recreate the hybrid table with the indexes defined. See also Create hybrid tables and Index hybrid tables.

See also:

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

语法

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

参数

index_name

指定新索引的标识符。您必须为给定混合表上的每个新索引指定一个唯一的名称。混合表上不能存在具有相同名称或相同有序列集的其他二级索引。

table_name

指定用于保存新索引的现有混合表的名称。

col_name

指定混合表中现有列的名称。创建表时定义的索引列的所有要求都适用于列标识符。

混合表不能包含在相同有序列集上定义的两个二级索引。

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

可选参数

INCLUDE ( col_name [ , col_name , ... ] )

Specifies one or more included columns for a secondary index. Using included columns with a secondary index is particularly useful when queries frequently contain a set of columns in the SELECT list but not in the list of WHERE predicates. For more information, see INCLUDE columns.

INCLUDE 列不能是半结构化列（VARIANT、OBJECT、ARRAY）或地理空间列（GEOGRAPHY、GEOMETRY）。

访问控制要求

要创建索引，您必须使用对混合表具有 OWNERSHIP 权限的角色。

使用说明

  • CREATE INDEX 命令不能用于添加外键、主键或唯一键约束。

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

  • 在任何时候，每个混合表都只能运行一个活动的索引构建操作。

  • 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.

  • 在索引构建过程中，任何 DML 都会执行对新索引的写入操作，但不使用该索引检索数据。

  • 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.

  • Indexed columns do not support collations. For more information, see Collations on hybrid table columns and Collation control.

  • The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.

  • CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

示例

要运行以下 CREATE INDEX 示例，请首先创建并加载混合表。

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

现在您可以在表上创建索引。

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

如果在构建索引时失败，SHOW INDEXES 命令会报告以下状态：

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;
+-------------------------------------+
| status                              |
%-------------------------------------%
| Statement executed successfully.    |
+-------------------------------------+