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

where

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

• UNIQUE indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.

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

• column_list specifies 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 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

The Oracle mode of OceanBase Database 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 function-based indexes by executing SQL statements

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

where

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

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

• table_name specifies the name of the table on which the function-based index is to be created.

• expr specifies 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 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);

where

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

• UNIQUE indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.

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

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

• STORING(column_name, ...) specifies 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 columnstore format.

• WITH COLUMN GROUP([all columns, ]each column) specifies the columnstore attribute of the index.

  • WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore-columnstore redundant 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. 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);