#CREATE INDEX
# 通用描述
CREATE INDEX用于在指定的表上创建一个索引对象。在YashanDB中可创建多种类型的索引,例如:
# 语句定义
create index::=
index_expr::=
index_partition_attr_clause::=
index_comp_partition_clause::=
# unique
该语句用于指定创建的索引为唯一索引,即索引列字段上的数值唯一。如表中该列字段上的值存在重复,则创建唯一索引失败。
LSC表必须指定该关键字。
分布式部署中,分区列必须是唯一索引关键列的子集,否则报错。
示例
--在表orders_info中的id,area列创建唯一索引idx_orders_info_1
CREATE UNIQUE INDEX idx_orders_info_1 ON orders_info (id, area);
# columnar
该语句用于指定创建的索引为列式索引,即索引的数据内容采用列存方式存储,索引本身无序。
列式索引存在如下限制:
示例(单机HEAP表)
--在表orders_info中的product_no列创建列式索引colidx_orders_info_1
CREATE COLUMNAR INDEX colidx_orders_info_1 ON orders_info (product_no);
# rtree
该语句用于指定创建RTREE索引。
RTREE索引存在如下限制:
- 当指定为RTREE索引时,不能同时指定为函数索引或反向索引,且不能在线(ONLINE)创建和重建(rebuild_clause)。
- 每次执行该语句时,所指定的列中仅允许存在1个ST_Geometry类型(其他类型不受此限制)。如需在同一张表中为多个ST_Geometry类型列创建RTREE索引,需重复执行该语句并指定不同列。
- RTREE索引仅适用于单机部署HEAP表。
- 不能为临时表创建RTREE索引。
使用RTREE索引时,不能将事务隔离级别设为SERIALIZABLE。
示例(单机HEAP表)
--在表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表创建函数索引。
- 当指定为函数索引时,不能指定NOPARALLEL|PARALLEL语句。
示例(HEAP表,单机TAC表)
--函数索引可以为任意表达式
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
指定索引列的同时指定排序方式,可省略,则系统默认按升序排序。
示例(HEAP表、TAC表)
--指定索引列的升降序
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。
# usable|unusable
该语句用于指定创建的索引是否可用,省略则默认为USABLE。
# global
该语句用于显式指定创建的索引是全局索引。若不指定该关键字,YashanDB也将默认创建全局索引。分布表指定GLOBAL关键字将会报错。
# online
该语句用于指定创建索引过程中是否允许并发DML操作,省略则默认不允许。
在线(Online)创建索引存在如下限制:
# noparallel|parallel
该语句用于设置创建索引的并行度,NOPARALLEL表示不并行。
LSC表不允许并行创建索引,即integer不得大于1。
integer
并行度值,取值范围为[1,服务器CPU核数*2],可省略,省略则默认按CPU核数的并行度并发创建索引。
示例(单机HEAP表、单机TAC表)
--在线并行创建索引
DROP INDEX idx_orders_info_1;
CREATE INDEX idx_orders_info_1 ON orders_info (id) ONLINE PARALLEL 2;
# nocompress|compress
该语句用于语法兼容,无实际含义。
# logging|nologging
该语句用于指定创建索引的logging属性,可省略,省略则默认为logging。
只能在nologging表上创建nologging索引。
示例(HEAP表、TAC表)
CREATE TABLE logging_table(c1 INT);
CREATE INDEX logging_index ON logging_table(c1);
-- 只能在nologging表上创建nologging索引
CREATE INDEX nologging_index ON logging_table(c1) NOLOGGING;
YAS-02367 create nologging index is not allowed for logging table
# noreverse|reverse
该语句用于指定创建的索引是否为反向索引。反向索引在存入数据时将索引列字节进行反转,以离散数据,减少叶块争用。省略时默认为NOREVERSE。
不能为LSC表创建反向索引。
示例(HEAP表、TAC表)
DROP INDEX idx_orders_info_1;
CREATE INDEX idx_orders_info_1 ON orders_info (id) REVERSE;
DROP INDEX idx_orders_info_1;
CREATE INDEX idx_orders_info_1 ON orders_info (id) NOREVERSE;
# readonly_clause
语法兼容,无实际含义。
# inmemory_clause
语法兼容,无实际含义。
# local_index_clause
该语句用于创建分区索引(Local Partitioned Index)。只能在分区表上建立分区索引,且索引的分区数量、分区界值必须与表的分区相同。
创建唯一索引,要保证一级分区键和二级分区键的并集需要是索引键的子集。
如LOCAL后面的语句都省略,即按如下规则创建索引分区:
- 分区数量与表分区数量一致
- 分区界值与表分区界值一致
- 表空间与在INDEX上指定的值一致
- INITRANS/PCTFREE等存储属性与在INDEX上指定的值一致
- USABLE/UNUSABLE开关设置为USABLE
示例(HEAP表、单机TAC表)
--创建本地分区索引
CREATE INDEX idx_sales_info_2 ON sales_info (year,month,branch)
TABLESPACE yashan
INITRANS 3
UNUSABLE
LOCAL;
# store_in_partition_clause
该语句仅被用于指定在哈希分区表上的索引分区的表空间,指定多个表分区用,
分隔,且数量需与索引分区的数量一致。
示例(HEAP表、单机TAC表)
--指定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。
示例(HEAP表、单机TAC表)
--指定索引分区属性
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 UNIQUE INDEX idx_orders_info_sub ON orders_info(area, id) LOCAL;
示例(单机/共享集群部署、HEAP/TAC表)
--创建指定名称的二级分区索引
CREATE INDEX idx_sales_info_sub ON sales_info(product) LOCAL
(PARTITION ip_sales_info_1 UNUSABLE(SUBPARTITION isp_sales_info_11, SUBPARTITION isp_sales_info_12, SUBPARTITION isp_sales_info_13),
PARTITION ip_sales_info_2 USABLE (SUBPARTITION isp_sales_info_21, SUBPARTITION isp_sales_info_22, SUBPARTITION isp_sales_info_23),
PARTITION ip_sales_info_3 USABLE (SUBPARTITION isp_sales_info_31, SUBPARTITION isp_sales_info_32, SUBPARTITION isp_sales_info_33));