Create an index

This topic will guide you through creating indexes using SQL statements. It will also cover the prerequisites for index creation, provide an overview of indexes, discuss limitations and recommendations, and include several examples.

Overview

An index, also known as a secondary index, is an optional structure. OceanBase Database uses the clustered index table model. The system automatically generates a primary key index for the specified primary key, and other indexes that you create are secondary indexes. You can determine the fields on which indexes are to be created based on business needs to speed up queries on these fields.

For more information about indexes in OceanBase Database, see About indexes.

Prerequisites

Before you create an index, make sure that:

• You have deployed an OceanBase cluster and created an Oracle tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to the Oracle tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Overview of connection methods.

• You have created a table. For more information, see Create a table.

• You have the INDEX privilege and the ALTER privilege on the table on which an index is to be created. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Limitations

• In OceanBase Database, the index name must be unique within the table.

• The length of the index name cannot exceed 128 bytes.

• Multiple unique indexes can be created on a table, but each unique index must ensure the uniqueness of the corresponding column values.

• If you want to use a local unique index to enforce data uniqueness, the local unique index must include the table partitioning key.

• Limitations on function indexes

  In Oracle-compatible mode of OceanBase Database, there are limitations on the expressions used in function indexes. Certain system functions are not allowed to be used as function index expressions. For detailed lists of supported and unsupported system functions, see Supported system functions for function indexes and Unsupported system functions for function indexes.

Recommendations for creating indexes

• We recommend that you use a name that clearly describes the columns included in the index and its purpose, such as idx_customer_name. For more information about naming conventions, see Overview of object naming conventions.

• If the partitioning rules and number of partitions of a global index are the same as those of the primary table, we recommend that you create a local index.

• We recommend that you set the maximum number of concurrent index creation statements to no more than the number of CPU cores specified in the tenant unit specification. For example, if the tenant unit specification specifies 4 CPU cores (4C), we recommend that you create no more than four indexes in parallel.

• Avoid creating too many indexes on frequently updated tables. Create indexes on frequently queried columns.

• We recommend that you do not create indexes on tables with a small amount of data. This is because queries may run faster when scanning all the data rather than traversing the index.

• We recommend that you do not create indexes when the modification performance is much higher than the retrieval performance.

• To create an efficient index:

  • The index should contain all the columns required for queries. The more columns included, the fewer rows need to be fetched from the table.

  • The equality condition should always be placed at the beginning.

  • The filter and sort conditions that involve a large amount of data should be placed at the beginning.

Create an index by using a command

You can use the CREATE INDEX statement to create an index.

Examples

Example 1: Create a unique index

If you need to ensure that there are no duplicate values in the indexed column, you can create a unique (UNIQUE) index.

Use the following SQL statements to create a table named TEST_TBL1, and then create a unique index on the col2 column of the TEST_TBL1 table.

1. Create the TEST_TBL1 table.

   CREATE TABLE TEST_TBL1(col1 NUMBER, col2 NUMBER, col3 VARCHAR2(50), PRIMARY KEY(col1));
   

2. Create a unique index named IDX_TEST_TBL1_COL2 on the col2 column of the TEST_TBL1 table.

   CREATE UNIQUE INDEX IDX_TEST_TBL1_COL2 ON TEST_TBL1(col2);
   

3. View the index information of the TEST_TBL1 table.

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL1';
   

   The return result is as follows:

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL1_OBPK_1703316804944854 | NORMAL     | SYS         | TEST_TBL1  | UNIQUE     |
   | IDX_TEST_TBL1_COL2              | NORMAL     | SYS         | TEST_TBL1  | UNIQUE     |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

Example 2: Create a non-unique index

Use the following SQL statements to create a table named TEST_TBL2, and then create an index on the col2 column of the TEST_TBL2 table.

1. Create the TEST_TBL2 table.

   CREATE TABLE TEST_TBL2(col1 NUMBER, col2 NUMBER, col3 VARCHAR2(50), PRIMARY KEY(col1));
   

2. Create an index named IDX_TEST_TBL2_COL2 on the col2 column of the TEST_TBL2 table.

   CREATE INDEX IDX_TEST_TBL2_COL2 ON TEST_TBL2(col2);
   

3. View the index information of the TEST_TBL2 table.

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL2';
   

   The return result is as follows:

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL2_OBPK_1703317409002143 | NORMAL     | SYS         | TEST_TBL2  | UNIQUE     |
   | IDX_TEST_TBL2_COL2              | NORMAL     | SYS         | TEST_TBL2  | NONUNIQUE  |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

Example 3: Create a local index

A local index is an index created on the data of a single partition. This means that the index key values in a local index have a one-to-one correspondence with the data in the table: each partition in the local index matches exactly one partition in the table. They share the same partitioning rules. Therefore, a local unique index can only guarantee uniqueness within each partition, not across the entire table. The keyword for creating a local index is LOCAL.

If you want to use a local unique index to enforce data uniqueness, the local unique index must include the table's partitioning key.

Use the following SQL statements to create a partitioned table named TBL3_F_RL, and then create a local unique index on the col1 and col2 columns of the TBL3_F_RL table.

1. Create a Range + List partitioned table named TBL3_F_RL.

   CREATE TABLE TBL3_F_RL(col1 NUMBER,col2 NUMBER) 
     PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
     (PARTITION p0 VALUES LESS THAN(100)
       (SUBPARTITION sp0 VALUES(1,3),
        SUBPARTITION sp1 VALUES(4,6),
        SUBPARTITION sp2 VALUES(7,9)),
      PARTITION p1 VALUES LESS THAN(200)
       (SUBPARTITION sp3 VALUES(1,3),
        SUBPARTITION sp4 VALUES(4,6),
        SUBPARTITION sp5 VALUES(7,9))
      ); 
   

2. Create a local unique index named IDX_TBL3_F_RL_COL1_COL2 on the col1 and col2 columns of the TBL3_F_RL table.

   CREATE UNIQUE INDEX IDX_TBL3_F_RL_COL1_COL2 ON TBL3_F_RL(col1,col2) LOCAL;
   

3. View the index information of the TBL3_F_RL table.

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TBL3_F_RL';
   

   The return result is as follows:

   +-------------------------+------------+-------------+------------+------------+
   | INDEX_NAME              | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +-------------------------+------------+-------------+------------+------------+
   | IDX_TBL3_F_RL_COL1_COL2 | NORMAL     | SYS         | TBL3_F_RL  | UNIQUE     |
   +-------------------------+------------+-------------+------------+------------+
   1 row in set
   

Example 4: Create a global index

The GLOBAL keyword is used to create a global index. Compared with a local index, a global index has the following characteristics: the partitioning rules of a global index are independent of those of the table, and you can specify the partitioning rules and the number of partitions of a global index. They do not need to be consistent with those of the table.

• In Oracle-compatible mode of OceanBase Database, if the index attribute keyword is not specified, the default index attribute is GLOBAL, which indicates that the index is a global index and the index table has only one partition.

• The partitioning rules of a global index do not need to be consistent with those of the table.

• If the partitioning rules and the number of partitions of a global index are the same as those of the primary table, we recommend that you create a local index. This is because the maintenance cost of a global index is higher, and the physical locations of a global index and the primary table partitions are not guaranteed to be the same unless they are specified to be in the same table group.

Run the following SQL statements to create a partitioned table named TBL4_H and create a global index named IDX_TBL4_H_COL2 on the col2 column of the TBL4_H table.

1. Create a Hash-partitioned table named TBL4_H.

   CREATE TABLE TBL4_H(col1 NUMBER PRIMARY KEY,col2 NUMBER)
     PARTITION BY HASH(col1) PARTITIONS 5;
   

2. Create a global index named IDX_TBL4_H_COL2 on the col2 column of the TBL4_H table. The index is a Range-partitioned index.

   CREATE INDEX IDX_TBL4_H_COL2 ON TBL4_H(col2) GLOBAL
     PARTITION BY RANGE(col2)
      (PARTITION p0 VALUES LESS THAN(100), 
       PARTITION p1 VALUES LESS THAN(200), 
       PARTITION p2 VALUES LESS THAN(300)
      );
   

3. View the index information of the TBL4_H table.

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TBL4_H';
   

   The return result is as follows:

   +------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                   | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +------------------------------+------------+-------------+------------+------------+
   | TBL4_H_OBPK_1703321659273683 | NORMAL     | SYS         | TBL4_H     | UNIQUE     |
   | IDX_TBL4_H_COL2              | NORMAL     | SYS         | TBL4_H     | NONUNIQUE  |
   +------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

Example 5: Create a function index

An index created based on the values of one or more columns in a table is called a function index. A function index is an optimization technique. When you use a function index, the system can quickly locate the matching function values during queries, thus avoiding repeated calculations and improving query efficiency.

In OceanBase Database's Oracle-compatible mode, the expressions of some system functions are not allowed to be used as function indexes. For more information, see List of system functions supported by function indexes and List of system functions not supported by function indexes.

Session variable persistence mechanism

The session variable persistence mechanism for function indexes ensures that the calculation semantics of the index remain completely consistent between creation and usage. This prevents situations where an index is used but returns incorrect results due to changes in the session environment.

How it works

When you create a function index, the system automatically analyzes the index expression to identify which session variables—such as time_zone, nls_date_format, and nls_timestamp_format—it depends on. The current values of these variables are then captured and stored as part of the index metadata. When a query uses a function index:

1. The system retrieves the snapshot of session variables saved at the time the index was created, using the local_session_var_id from the index expression.
2. During type inference and calculation, the system uses these stored variable values instead of the current session’s values.
3. This ensures that calculation results are always consistent with those at the time the index was created.

Supported session variables

Function indexes can persist the following session variables:

The system dynamically determines which variables need to be persisted based on the index expression. For example, if the index expression includes time functions such as SYSDATE or CURRENT_TIMESTAMP, the time_zone variable will be persisted; if it includes date formatting functions, the relevant NLS format variables will be persisted; if it includes string functions, the collation_connection variable may be persisted.

Considerations

Avoid using volatile expressions Although the system automatically persists session variables, it is recommended to minimize the use of functions affected by session variables in indexes. Whenever possible, use deterministic columns:

-- ❌ Not recommended (depends on time_zone)
CREATE INDEX idx_now ON logs ((DATE(NOW())));

-- ✅ Recommended (uses a deterministic column)
CREATE INDEX idx_log_date ON logs ((DATE(log_time)));

No manual session variable management required

The system automatically handles session variable consistency, so you do not need to worry that changes to the current session variables will affect the correctness of existing function indexes. During queries, the system will always use the variable snapshot saved at the time the index was created.

Use the following SQL statement to create a table named TEST_TBL5 and create a function index based on the id column in the table TEST_TBL5.

1. Create the table TEST_TBL5.

   CREATE TABLE TEST_TBL5(id NUMBER, name VARCHAR2(18));
   

2. Create a function index named IDX_TEST_TBL5_ID based on the id column in the table TEST_TBL5.

   CREATE INDEX IDX_TEST_TBL5_ID ON TEST_TBL5 ((id+1));
   

3. Use the following SQL statement to view the created function index.

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL5';
   

   The returned result is as follows:

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

Example 6: Create a spatial index

A spatial index is a type of database index used to process and optimize spatial data. It is widely used in geographic information systems (GIS) and for storing and querying location data. The syntax for creating a spatial index in OceanBase Database differs from that in Oracle Database. The SRID of a spatial index column is specified when the table is created.

Consider the following points when you create a spatial index:

• Before you use GIS features, you must configure GIS meta data for the business tenant. For more information, see Preparations.
• For information about constraints when you create a spatial index for a partitioned table, see Constraints.

1. Create a table named TEST_TBL6.

   obclient [test]> CREATE TABLE TEST_TBL6(id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY SRID 4326);
   

2. Create a spatial index named IDX_TBL6_G on the shape column of the TEST_TBL6 table.

   obclient [test]> CREATE INDEX IDX_TBL6_G ON TEST_TBL6(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
   

3. Query the index information of the TEST_TBL6 table.

   obclient [test]> SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL6';
   

   The returned result is as follows:

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL6_OBPK_1718852454772761 | NORMAL     | SYS         | TEST_TBL6  | UNIQUE     |
   | IDX_TBL6_G                      | DOMAIN     | SYS         | TEST_TBL6  | NONUNIQUE  |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

Example 7: Create a columnstore index

After you create a table named TEST_TBL7, create a columnstore index named IDX1_TBL7_CG.

1. Create a table named TEST_TBL7.

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

2. Create a columnstore index named IDX1_TBL7_CG on the TEST_TBL7 table and store redundant data of the age column in the index table.

   CREATE INDEX IDX1_TBL7_CG ON TEST_TBL7(name) STORING(age) WITH COLUMN GROUP(each column);
   

3. Query the index information of the TEST_TBL7 table.

   obclient [test]> SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL7';
   

   The returned result is as follows:

   +--------------+------------+-------------+------------+------------+
   | INDEX_NAME   | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +--------------+------------+-------------+------------+------------+
   | IDX1_TBL7_CG | NORMAL     | SYS         | TEST_TBL7  | NONUNIQUE  |
   +--------------+------------+-------------+------------+------------+
   1 row in set (0.026 sec)
   

What to do next

After you create an index, you may need to optimize query performance. For more information about SQL tuning, see Overview of SQL tuning.

References

• For more information about how to view an index, see View an index.
• For more information about how to manage an index, see DROP INDEX and Delete an index.
• For more information about unique and non-unique indexes, see Unique and non-unique indexes.
• For more information about local and global indexes, see Local and global indexes.