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 in OceanBase Database, the index name must be unique within the schema.

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

Parameter description:

• 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 table tbl2 and then create index 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

OceanBase Database in Oracle-compatible 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);

Parameter description:

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

Parameter description:

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

Parameter description:

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

Create a full-text index

In the Oracle-compatible mode of OceanBase Database, you can create a full-text index after creating a table using the CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT ... statement. For more information about full-text indexes, see Full-text indexes.

Limitations and considerations for full-text indexes

• The following types of tables support creating full-text indexes:

  • Regular tables
  • Partitioned tables (RANGE, HASH, LIST, composite partitions)
  • Replicated tables
  • Heap-organized tables (heap tables)
  • Columnar storage tables

• The current version's full-text indexes only support columns of the CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2, and CLOB data types.

• The current version only supports creating local (LOCAL) full-text indexes.

• Index names must be unique within the schema.

• Multiple columns cannot be used to jointly create a full-text index.

• Full-text indexes cannot be created on generated columns.

• Full-text indexes cannot be created on views.

• Full-text indexes and regular indexes cannot have the same name.

• Columns with full-text indexes cannot be dropped.

• Column types with full-text indexes cannot be changed to integer types.

Syntax for creating a full-text index

You can use the CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT ... statement to create a full-text index on an existing table. The syntax is as follows:

CREATE INDEX [schema.]index_name ON [schema.]table_name(column_name)
    INDEXTYPE IS CTXSYS.CONTEXT
    [PARAMETERS('LEXER parser_name')]
    LOCAL;

parser_name:
    SPACE
    | NGRAM
    | NGRAM2
    | IK
    | BENG

The relevant parameters are described as follows:

• schema.: Optional. When you need to create an index on a table in another schema, you must include that schema name. If schema. is omitted, the index/table is assumed to be in the current schema by default.

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

• table_name: Specifies the name of the table on which to create the index.

• column_name: Specifies the column for which to create the index.

• PARAMETERS('LEXER parser_name'): Optional. Specifies the tokenizer for the full-text index. If PARAMETERS('LEXER parser_name') is not explicitly specified, the tokenizer defaults to SPACE, which is suitable for languages like English that use spaces to separate semantics. parser_name indicates the tokenizer name. Valid values are as follows:

  • SPACE: Default value, indicating to tokenize by space.
  • NGRAM: Indicates a word segmentation method based on N-Grams (for Chinese).
  • NGRAM2: Indicates to split text into consecutive characters within a set range.
  • IK: Indicates a word segmentation method based on IK (for Chinese).
  • BENG: Indicates a word segmentation method based on Beng (basic English).

  For detailed information on the strategy for selecting a tokenizer, see the Tokenizers for full-text indexes section under Full-text indexes.

• LOCAL: Specifies to create a local index.

Example:

After creating the tbl5 table, create a full-text index named ft_idx1_tbl5.

1. Create the tbl5 table.

   obclient> CREATE TABLE tbl5(col1 INT, col2 VARCHAR2(50));
   

2. Create a full-text index named ft_idx1_tbl5 on the tbl5 table, specifying the IK tokenizer for the full-text index.

   obclient> CREATE INDEX ft_idx1_tbl5 ON tbl5(col2)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER IK')
       LOCAL;
   

References

• For more information about the CREATE INDEX syntax, see CREATE INDEX.
• For information about querying data using full-text indexes, see Full-text queries.