Create an index

This topic describes how to create an index by using an SQL statement, including the prerequisites, index overview, limitations, and recommendations, with some examples.

Index 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-compatible tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to an Oracle-compatible 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 for creating an index

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

• 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-based indexes

  In the Oracle-compatible mode of OceanBase Database, restrictions are imposed on the expressions of function-based indexes. Specifically, the expressions of some system functions are not allowed to be used as function-based indexes. For the list of functions, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

Recommendations for creating an index

• We recommend that you use names that succinctly describe the columns covered by the index and its purpose, for example, idx_customer_name. For more information about naming conventions, see Object naming conventions.

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

• We recommend that you limit the number of SQL statements for parallel index creation to the maximum number of CPU cores specified in the tenant's unit specifications. For example, if the tenant's unit specification is 4 CPU cores (4C), it is recommended to have no more than 4 index creation statements executed concurrently.

• We recommend that you create indexes on fields that are frequently used for queries, but do not create excessive indexes on tables that are frequently updated.

• Do not create indexes on tables with a small amount of data. For a table with a small data amount, it may take a shorter time to query all the data than to traverse the indexes. In this case, indexes cannot produce optimization effects.

• If modification requirements are far more than retrieval requirements, do not create indexes.

• Create efficient indexes:

  • Indexes must be created on all the columns to be queried. This can reduce the number of rows returned from table access by index primary key.

  • Indexes of equivalent conditions should always be placed in the front of the index table.

  • Indexes for large data filtering and sorting should be placed in the front of the index table.

Create an index by using the CLI

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

Examples

Example 1: Create a unique index

If there is a need to ensure that the values in an indexed column are unique, a UNIQUE index can be created.

Use the following SQL statements to create a table named test_tbl1 and to create a unique index based on the col2 column in test_tbl1.

1. Create a table named test_tbl1.

   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 in the test_tbl1 table.

   CREATE UNIQUE INDEX idx_test_tbl1_col2 ON test_tbl1(col2);
   

3. Query indexes of the test_tbl1 table.

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

   The 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

Execute the following SQL statements to create a table named test_tbl2 and create an index on the col2 column in the test_tbl2 table.

1. Create a table named test_tbl2.

   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 in the test_tbl2 table.

   CREATE INDEX idx_test_tbl2_col2 ON test_tbl2(col2);
   

3. Query indexes of the test_tbl2 table.

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

   The 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 created on data in a single partition. The key-value pairs of the index and the data in the table are in a one-to-one match. Each index partition maps to one table partition. They share the same partitioning rules. Therefore, a local unique index is only guaranteed to be unique in a partition. Its uniqueness within the table is not guaranteed. The key word for creating a local index is LOCAL.

To use a local unique index as a unique constraint on a table, the local unique index must contain a partitioning key of the table.

Execute the following SQL statements to create a subpartitioned table named tbl3_f_rl and create a local unique index on the col1 and col2 columns in the tbl3_f_rl table.

1. Create a RANGE-LIST-subpartitioned table named tbl3_f_rl without using a template.

   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 an index named idx_tbl3_f_rl_col1_col2 on the col1 and col2 columns in the tbl3_f_rl table.

   CREATE UNIQUE INDEX idx_tbl3_f_rl_col1_col2 ON tbl3_f_rl(col1,col2) LOCAL;
   

3. Query indexes 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 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 keyword for creating a global index is GLOBAL. Unlike a local index, the partitioning of a global index is independent of the partitioning of the table. You can specify the partitioning rules and the number of partitions for a global index. These rules and this number do not have to be the same as those of the table.

• If you do not specify the LOCAL or GLOBAL attribute for an index, OceanBase Database in Oracle-compatible mode defines this index as a GLOBAL index, and the index table has only one partition.

• The partitioning rules of a global index are not necessarily the same as those of the table.

• If a global index has the same partitioning rules and the same number of partitions as the table, we recommend that you create a local index in this case. The reasons are twofold. First, global indexes cost higher to maintain. Second, the physical storage location of a table and a global index are not necessarily the same, unless you manually include them in the same table group.

Execute the following SQL statements to create a partitioned table named tbl4_h and create a global index on the col2 column in 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 RANGE-partitioned global index named idx_tbl4_h_col2 on the col2 column in the tbl4_h table.

   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. Query indexes of the tbl4_h table.

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

   The 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-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.

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.

Session variable solidification mechanism

The session variable solidification mechanism for function-based indexes ensures that the computation semantics of the index at creation time and at query time are exactly the same, avoiding "index hit but incorrect result" scenarios caused by session environment changes.

How it works

When you create a function-based index, the system automatically analyzes the index expression, identifies which session variables (such as time_zone, nls_date_format, nls_timestamp_format, and so on) the expression depends on, and saves the current values of these variables to the index metadata.

When a query hits the function-based index:

1. The system obtains the session variable snapshot saved at index creation time based on the local_session_var_id of the index expression
2. Uses the saved variable values instead of the current session variable values during expression type inference and computation
3. Ensures that the computation result is exactly the same as at index creation time

Supported session variables

The session variables that can be solidified for function-based indexes include:

The system dynamically infers which variables need to be solidified based on the index expression. For example, if the index expression contains time functions such as SYSDATE or CURRENT_TIMESTAMP, time_zone is solidified; if it contains date formatting functions, the corresponding NLS format variables are solidified; if it contains string functions, collation_connection may be solidified.

Usage notes

Avoid relying on volatile expressions

Although the system automatically solidifies session variables, we recommend that you minimize the use of session-dependent functions in indexes and prefer deterministic columns:

-- Not recommended (depends on time_zone)
CREATE INDEX idx_sysdate ON logs ((TRUNC(SYSDATE)));

-- Recommended (uses deterministic column)
CREATE INDEX idx_log_date ON logs ((TRUNC(log_time)));

No need to manually manage session variables

The system automatically handles session variable consistency. You do not need to worry about changes to the current session variables affecting the correctness of existing function-based indexes. The saved variable snapshot from index creation time is automatically used at query time.

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

1. Create a table named TEST_TBL5.

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

2. Create an index named IDX_TEST_TBL5_ID on the id column in the TEST_TBL5 table.

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

3. Query the created function-based index.

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

   The 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 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 of Oracle, as the SRID of the spatial index column must be specified when the table is created.

When creating a spatial index, note the following:

• Before using the GIS feature, you need to configure GIS meta data in the business tenant. For detailed steps, see Preparations for creating a spatial index.
• For constraints related to creating a spatial index on partitioned tables, see Limitations on creating a spatial index.

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. View 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 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 columnar index

Create a table `TEST_TBL7` first, and then create a columnar index `IDX1_TBL7_CG`.

1. Create a table named `TEST_TBL7`.

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

2. Create a columnar index 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. View 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 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 creating an index, you may need to optimize query performance. For more information on SQL tuning, see SQL tuning.

References

• For more information about how to query indexes, see Query indexes.
• For more information about how to manage indexes, see DROP INDEX and Drop 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.