创建索引

本节主要介绍如何创建索引。

背景信息

OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或非唯一索引，还可以是函数索引。

本节主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 创建分区表索引 章节。

注意事项

创建索引时需要注意，在 OceanBase 数据库中，索引名称在 Schema 范围内不能重复。

创建普通索引

可以使用 CREATE INDEX 语句来创建普通索引。语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name );

相关参数说明如下：

• index_name：指定待添加的索引的索引名。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• table_name：指定待添加索引的表的表名。

• column_list：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

创建表 tbl2 后，再创建索引 tbl2_idx1 的示例如下：

1. 创建表 tbl2。

   CREATE TABLE tbl2(id NUMBER,name VARCHAR2(20));
   

2. 在表 tbl2 上创建索引 tbl2_idx1。

   CREATE INDEX tbl2_idx1 ON tbl2(name);
   

3. 查看索引信息。

   SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='TBL2';
   

   结果如下：

   +------------+------------+-------------+------------+------------+
   | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +------------+------------+-------------+------------+------------+
   | TBL2_IDX1  | NORMAL     | SYS         | TBL2       | NONUNIQUE  |
   +------------+------------+-------------+------------+------------+
   1 row in set
   

创建函数索引

基于表中一列或多列的值进行计算后的结果建立的索引称为函数索引。函数索引是一种优化技术，使用函数索引可以在查询时快速定位匹配的函数值，从而避免重复计算，提高查询效率。

假设您有一张表 sale，表中有一个数据类型为 date 的列 date_col：

CREATE TABLE sale(date_col date, sales_col int, name_col varchar2(10));

如果您需要获取 3 月份的数据，则可以使用 to_char 函数来查询数据：

SELECT * FROM sale WHERE to_char(date_col,'MM') = 3;

该场景下，数据库需要为表中的每行计算 to_char(date_col,'MM')，并过滤掉不符合 to_char(date_col,'MM') = 3 的行。如果您需要频繁地使用月份信息来过滤数据，则每次都需要重新计算 to_char(date_col,'MM')，就会造成大量的开销。因此，您可以在 date_col 上创建一个函数索引，将 to_char(date_col,'MM') 存储到索引表中，来加速查询。

CREATE INDEX idx1 ON sale((to_char(date_col,'MM')));

函数索引使用限制

在 OceanBase 数据库的 Oracle 模式中，对函数索引的表达式进行了限制，禁止部分系统函数的表达式作为函数索引，具体的函数列表请参见 函数索引支持的系统函数列表 和 函数索引不支持的系统函数列表。

函数索引的创建

您可以使用 CREATE INDEX 语句来创建函数索引。函数索引也可以是唯一索引。语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name (expr);

相关参数说明如下：

• [UNIQUE]：可选，表示唯一索引。创建唯一索引时需要添加对应的关键字。

• index_name：待创建的函数索引的名称。

• table_name：指定待创建索引的表的表名。

• expr：合法的函数索引表达式，不能是布尔表达式，例如 c1=c1 不支持。

在表上创建一个索引定义为 c1+1 的函数索引 tbl2_func_idx1，示例如下：

1. 创建表 tbl2_func。

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. 创建函数索引 tbl2_func_idx1。

   CREATE INDEX tbl2_func_idx1 ON TBL2_FUNC ((c1+1));
   

3. 创建成功后，可以查看创建的函数索引。

   SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='TBL2_FUNC';
   

   结果如下：

   +----------------+-----------------------+-------------+------------+------------+
   | INDEX_NAME     | INDEX_TYPE            | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +----------------+-----------------------+-------------+------------+------------+
   | TBL2_FUNC_IDX1 | FUNCTION-BASED NORMAL | SYS         | TBL2_FUNC  | NONUNIQUE  |
   +----------------+-----------------------+-------------+------------+------------+
   1 row in set
   

创建列存索引

可以使用 CREATE INDEX 语句来创建列存索引。语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name ) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

相关参数说明如下：

• index_name：指定待添加的索引的索引名。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• table_name：指定待添加索引的表的表名。

• column_list：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

• STORING(column_name, ...)：可选，指定索引表上的冗余列。即使 column_name 列不是索引字段，也会按照列存格式进行冗余存储。

• WITH COLUMN GROUP([all columns, ]each column)：指定索引的列存属性。

  • WITH COLUMN GROUP(all columns, each column)：指定创建行存列存冗余的索引。
  • WITH COLUMN GROUP(all columns)：指定创建行存索引。
  • WITH COLUMN GROUP(each column)：指定创建列存索引。

示例如下：

创建表 tbl3 后，再创建列存索引 idx1_tbl3_cg。

1. 创建表 tbl3。

   CREATE TABLE tbl3(id NUMBER, name VARCHAR2(20), age NUMBER);
   

2. 在表 tbl3 上创建列存索引 idx1_tbl3_cg，并且在索引表上存储 age 列的冗余数据。

   CREATE INDEX idx1_tbl3_cg ON tbl3(name) STORING(age) WITH COLUMN GROUP(each column);
   

创建空间索引

可以使用 CREATE INDEX 语句来创建空间索引。语法如下：

CREATE INDEX index_name ON table_name(column_g_name) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

相关参数说明如下：

• index_name：指定待添加的索引的索引名。
• table_name：指定待创建索引的表的表名。
• column_g_name：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。待添加空间索引的列的列类型必须为空间数据类型。

示例如下：

1. 创建表 tbl4_g。

   CREATE TABLE tbl4_g (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(32), col3 SDO_GEOMETRY SRID 4326);
   

2. 为表创建空间索引 tbl4_g_idx1。

   CREATE INDEX tbl4_g_idx1 ON tbl4_g(col3) INDEXTYPE IS MDSYS.SPATIAL_INDEX;