Create an index

This topic describes how to create an index.

Background information

OceanBase Database allows you to create local indexes, global indexes, unique indexes, non-unique indexes, or function-based indexes on non-partitioned and partitioned tables.

This topic describes how to create an index on a non-partitioned table. For information about how to create an index on a partitioned table, see Create an index on a partitioned table.

Considerations

When you create an index on a table in OceanBase Database, make sure that the index name is unique within the table.

Create a normal index

You can use the CREATE INDEX statement to create a normal index. The syntax is as follows:

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

The parameters are described as follows:

• index_name: the name of the index to be created.

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

• table_name: the name of the table on which the index is to be created.

• column_list: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

Example: Create a table named tbl2 table and then create an index named tbl2_idx1.

1. Create a table named tbl2.

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

2. Create an index named tbl2_idx1 on the tbl2 table.

   CREATE INDEX tbl2_idx1 ON tbl2(name);
   

3. View the index information.

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

   The return result is as follows:

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

Create a function-based index

A function-based index is created based on the result of any function applied to a column or multiple columns. Function-based indexing is an optimization technique. You can use function-based indexes to quickly locate function values that match query conditions. This avoids repeated calculation and improves query efficiency.

Assume that you have a table named sale and that the table has a column named date_col of the date type.

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

If you want to obtain the data of March, you can use the to_char function.

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

In this scenario, the database must calculate the to_char(date_col,'MM') value for each row in the table and filter out rows that do not meet the condition to_char(date_col,'MM') = 3. If you need to frequently filter data by month and the to_char(date_col,'MM') values need to be calculated each time, the overhead is high. In this case, you can create a function-based index on the date_col column to store the to_char(date_col,'MM') values in an index table to accelerate queries.

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

Limitations

OceanBase Database in Oracle mode imposes limitations on expressions of function-based indexes. Specifically, the expressions of some system functions cannot be used as function-based indexes. For more information about the relevant functions, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

Create a function-based index

You can use the CREATE INDEX statement to create a function-based index. A function-based index can be a unique index. The syntax is as follows:

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

The parameters are described as follows:

• [UNIQUE]: specifies to create a unique index. This parameter is optional. This option is required when you create a unique index.

• index_name: the name of the function-based index to be created.

• table_name: the name of the table on which the index is to be created.

• expr: the expression of the function-based index. It cannot be a Boolean expression, such as c1=c1.

Example: Create a function-based index named tbl2_func_idx1 with an expression of c1+1.

1. Create a table named tbl2_func.

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. Create a function-based index named tbl2_func_idx1.

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

3. View the created function-based index.

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

   The return result is as follows:

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

Create a columnstore index

You can use the CREATE INDEX statement to create a columnstore index. The syntax is as follows:

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

The parameters are described as follows:

• index_name: the name of the index to be created.

• UNIQUE: specifies to create a unique index. This parameter is optional. You must add this keyword when you create a unique index.

• table_name: the name of the table on which the index is to be created.

• column_list: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

• STORING(column_name, ...): the redundant columns in the index table. This parameter is optional. Even though the column_name column is not an index column, it is redundantly stored in the columnar storage format.

• WITH COLUMN GROUP([all columns, ]each column): the columnar storage options for the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a hybrid rowstore-columnstore index.
  • WITH COLUMN GROUP(all columns): specifies to create a rowstore index.
  • WITH COLUMN GROUP(each column): specifies to create a columnstore index.

Here is an example:

Create a table named tbl3, and then create a columnstore index named idx1_tbl3_cg.

1. Create a table named tbl3.

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

2. On the tbl3 table, create a columnstore index named idx1_tbl3_cg and redundantly store the data of the age column in the index table.

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

Create a spatial index

You can use the CREATE INDEX statement to create a spatial index. The syntax is as follows:

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

The parameters are described as follows:

• index_name: the name of the index to be created.
• table_name: the name of the table on which the index is to be created.
• column_g_name: the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,). The column on which a spatial index is to be created must be of a spatial data type.

Here is an example:

1. Create a table named tbl4_g.

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

2. Create a spatial index named tbl4_g_idx1 on the table.

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