通用描述

CREATE INDEX用于在指定的表上创建一个索引对象。在YashanDB中可创建多种类型的索引，例如：

• 唯一索引
• 列式索引
• 函数索引
• 反向索引
• 分区索引
• RTREE索引

语句定义

create index::=

语法图

代码

syntax

index_expr::=

语法图

代码

syntax

index_attr_clause::=

语法图

代码

syntax

storage_clause定义

local_index_clause::=

语法图

代码

syntax

readonly_clause::=

语法图

代码

syntax

inmemory_clause::=

语法图

代码

syntax

store_in_clause::=

语法图

代码

syntax

index_partition_clause::=

语法图

代码

syntax

index_partition_attr_clause::=

语法图

代码

syntax

index_comp_partition_clause::=

语法图

代码

syntax

index_subpartition_clause::=

语法图

代码

syntax

unique

该语句用于指定创建的索引为唯一索引，即索引列字段上的数值唯一。如表中该列字段上的值存在重复，则创建唯一索引失败。

LSC表必须指定该关键字。

示例

--在表orders_info中的id列创建唯一索引idx_orders_info_1
CREATE UNIQUE INDEX idx_orders_info_1 ON orders_info (id);

columnar

该语句用于指定创建的索引为列式索引，即索引的数据内容采用列存方式存储，索引本身无序。

列式索引存在如下限制：

• 列式索引不支持创建函数索引及反向索引。
• 不支持在线（Online）创建列式索引。
• 只允许行表创建列式索引。
• 共享集群部署下不支持列式索引。

示例

--在表orders_info中的id列创建列式索引colidx_orders_info_1
CREATE COLUMNAR INDEX colidx_orders_info_1 ON orders_info (id);

rtree

该语句用于指定创建RTREE索引。

RTREE索引存在如下限制：

• RTREE索引只能在单一的ST_Geometry类型数据上创建。
• RTREE索引不支持可串行化事务。
• RTREE索引不支持创建在临时表中。
• RTREE索引不支持创建在LSC表中。

示例

--在表geom_test中的shp列创建RTREE索引idx_rtree_shp，其中shp列的数据类型为ST_Geometry
CREATE TABLE geom_test(id INT, geom_type VARCHAR(32), shp st_geometry);
CREATE RTREE INDEX idx_rtree_shp ON geom_test (shp);

index_name

该语句用于指定创建的索引的名称，不可省略，且需符合YashanDB的对象命名规范。

table_name

该语句用于指定要创建索引的表。创建索引的操作过程中会对该表加排他锁，但在指定了ONLINE时，本操作不会阻塞对该表的并发DML事务。

index_expr

指定索引所基于的列或列表达式，多项组合指定时以,分隔。

所有的索引列或列表达式里不能包含LOB大对象型的列字段。

column_name

将指定名称的列字段作为索引列。

column_expression

将指定的表达式作为索引列，含有表达式的索引列的索引称为函数索引。

YashanDB支持以任何通用表达式作为索引列来建立函数索引，但存在如下约束规则：

• column_expression计算出来的结果须满足系统对索引列的所有约束限制，例如不能为LOB类型，长度不能超过索引键值长度规格等。
• column_expression中包含内置函数时，该函数必须是一个可以返回确定和不变结果的非聚集函数，例如不能为SYSDATE、USERENV、SUM等函数。
• column_expression中存在自动填充行为时，不能为非固定自动填充行为的表达式，例如在日期转换时发生自动填充年月的行为，由于系统需使用当前年月这类非固定值填充，导致表达式输出的结果不能确定，所以无法建立函数索引；而对于时分秒系统固定按0填充，或者对于CHAR类型系统固定按空格补位，表达式输出结果确定，则可以建立函数索引。
• 在创建分区索引时，不能对分区键使用函数表达式。
• column_expression中不能包含用户自定义函数。
• RTREE索引不支持函数索引。
• LSC表不支持函数索引。

示例

--函数索引可以为任意表达式
CREATE UNIQUE INDEX idx_orders_info_2 ON orders_info (TO_CHAR(id)||order_no);
CREATE INDEX idx_orders_info_3 ON orders_info (CAST(id AS CHAR(14)),order_no);

--字符串向DATE转换时，未指定format将导致系统获取不能保证恒定的默认格式，所以无法返回一个不变的结果
CREATE INDEX idx_orders_info_4 ON orders_info (order_date-TO_DATE('2001-01-01'));
[1:69]YAS-04399 only pure functions can be indexed

desc|asc

指定索引列的同时指定排序方式，可省略，则系统默认按升序排序。

示例

--指定索引列的升降序
CREATE INDEX idx_orders_info_4 ON orders_info (order_date DESC, TO_NUMBER(order_no) ASC);

index_attr_clause

该语句用于指定索引的各项属性。

tablespace

该语句用于指定索引的表空间，省略则默认为当前用户所在的表空间。用户无法为建立在分布表上的索引和索引分区指定表空间，这类索引和索引分区所在的表空间将自动和表保持一致。

• tablespace_name：指定到一个存在的表空间。
• DEFAULT：指定到缺省表空间，即当前用户所在的表空间。

在临时表上创建的索引，其表空间只能为系统默认的temporary表空间，不可指定。

initrans/pctfree/storage_clause

该语句用于指定索引的存储属性，省略则INITRANS/PCTFREE默认为2/8，其他存储属性请参考通用SQL语法storage描述。

visible|invisible

该语句用于指定创建的索引是否能被优化器（Optimizer）使用，省略则默认为VISIBLE。

LSC表只支持指定创建的索引为INVISIBLE。

示例（LSC表）

CREATE UNIQUE INDEX idx_orders_info_4 ON orders_info (order_date DESC, TO_NUMBER(order_no) ASC) INVISIBLE;

usable|unusable

该语句用于指定创建的索引是否可用，省略则默认为USABLE。

online

该语句用于指定创建索引过程中是否允许并发DML操作，省略则默认不允许。分布式和共享集群部署中不支持执行create index online操作。YashanDB不支持RTREE索引的online操作。LSC表不支持任何形式索引的online操作。

noparallel|parallel

该语句用于设置创建索引的并行度，NOPARALLEL表示不并行。

YashanDB不支持对函数索引指定本语句。

LSC表不支持并行创建索引，即不支持指定并行度大于1的情况。

integer

并行度值，该值必须介于1~服务器CPU核数*2之间，可省略，则默认按CPU核数的并行度并发创建索引。

--在线并行创建索引
CREATE INDEX idx_orders_info_1 ON orders_info (id) ONLINE PARALLEL 2;

nocompress|compress

该语句用于语法兼容，无实际含义。

logging|nologging

该语句用于指定创建索引的logging属性，可省略，省略则默认为logging。

YashanDB不支持将nologging索引建立在logging表中。

示例

CREATE TABLE logging_table(c1 INT);

CREATE INDEX logging_index ON logging_table(c1);

--nologging索引不支持建立在logging表中
CREATE INDEX nologging_index ON logging_table(c1) NOLOGGING;
YAS-02367 create nologging index is not allowed for logging table

noreverse|reverse

该语句用于指定创建的索引是否为反向索引。反向索引在存入数据的时候将索引列字节进行反转，以离散数据，减少叶块争用。省略时默认为NOREVERSE。RTREE索引不支持指定REVERSE。LSC表不支持反向索引。

示例

CREATE INDEX idx_orders_info_1 ON orders_info (id) REVERSE;

CREATE INDEX idx_orders_info_1 ON orders_info (id) NOREVERSE;

readonly_clause

语法兼容，无实际含义。

inmemory_clause

语法兼容，无实际含义。

local_index_clause

该语句用于创建分区索引（Local Partitioned Index）。只能在分区表上建立分区索引，且索引的分区数量、分区界值必须与表的分区相同。

创建唯一索引，要保证一级分区键和二级分区键的并集需要是索引键的子集。

分布式下的分布表如不指定LOCAL关键字，默认也是创建分区索引，并且显式指定GLOBAL关键字将会报错。

如LOCAL后面的语句都省略，即按如下规则创建索引分区：

• 分区数量与表分区数量一致
• 分区界值与表分区界值一致
• 表空间与在INDEX上指定的值一致
• INITRANS/PCTFREE等存储属性与在INDEX上指定的值一致
• USABLE/UNUSABLE开关设置为USABLE

示例

--创建本地分区索引
CREATE INDEX idx_sales_info_2 ON sales_info (year,month,branch)
TABLESPACE yashan
INITRANS 3
UNUSABLE
LOCAL;

store_in_partition_clause

该语句仅被用于指定在哈希分区表上的索引分区的表空间，指定多个表分区用,分隔，且数量需与索引分区的数量一致。

示例

--指定hash分区索引的分区表空间
CREATE INDEX idx_sales_info_hash_1 ON sales_info_hash (year,month,branch)
TABLESPACE yashan
INITRANS 3
UNUSABLE
LOCAL STORE IN (yashan,yashan1);

index_partition_clause

指定索引分区的名称和其他属性，同时指定多个分区用,分隔，且数量需与索引分区的数量一致。

partition_name

指定索引分区的名称，省略则由系统生成的默认名称。

index_partition_attr_clause

指定索引分区的如下属性：

• 表空间：省略则默认为在INDEX上指定的表空间。其中DEFAULT值等于在INDEX上指定的表空间名称。
• INITRANS/PCTFREE：省略则默认为在INDEX上指定的存储属性值。
• USABLE/UNUSABLE：省略则为USABLE。

示例

--指定索引分区属性
CREATE INDEX idx_sales_info_range_1 ON sales_info_range (year,month,branch)
TABLESPACE yashan
INITRANS 3
UNUSABLE
LOCAL (PARTITION p1 TABLESPACE DEFAULT INITRANS 2,PARTITION p2,PARTITION p3);

index_comp_partition_clause

指定索引组合分区的名称和其他属性，同时指定多个分区用,分隔，且数量需与表分区的数量一致。

store in

指定二级分区模板信息，只适用于hash分区。

index_subpartition_clause

指定索引二级分区的名称和其他属性，同时指定多个二级分区用,分隔，且数量需与表的对应二级分区的数量一致。

示例

--创建二级分区索引
CREATE INDEX hh_idx ON HH_COMPOSITE(a) LOCAL
(PARTITION hh_pi1 UNUSABLE(SUBPARTITION hh_subpi1, SUBPARTITION hh_subpi2),
PARTITION hh_pi2 USABLE (SUBPARTITION hh_subpi3 UNUSABLE, SUBPARTITION hh_subpi4));