Create an index

This topic describes the definition of indexes, as well as the prerequisites, limitations, considerations, and examples for creating an index by using an SQL statement.

About indexes

An index, also known as a secondary index, is an optional structure. OceanBase Cloud 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.

Prerequisites

Before you create an index, make sure that:

• You have deployed an OceanBase cluster and created an Oracle-compatible tenant.

• You have connected to the Oracle-compatible tenant of OceanBase Cloud.

• You have created a table.

• You have the INDEX privilege and the ALTER privilege on the table on which an index is to be created. If you do not have the required privileges, contact the administrator to obtain the privileges.

Limitations

• In OceanBase Cloud, an index name must be unique in a table.

• An index name cannot exceed 128 bytes in length.

• You can create multiple unique indexes that have unique column values.

• To use a local unique index to ensure data uniqueness, this local unique index must contain the partitioning key of the table.

Considerations

• We recommend that you use a name that succinctly describes the columns covered by the index and its purpose, for example, idx_customer_name.

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

• We recommend that the number of parallel SQL statements that you issue to create indexes do not exceed the maximum number of CPU cores specified for the unit config of the tenant. For example, if the unit config of a tenant has 4 CPU cores, we recommend that you create no more than 4 indexes concurrently.

• Create indexes on fields that are frequently used for queries, but do not create excess 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:

  • Include all necessary columns required for queries. The more relevant columns included in the index, the better it can minimize the number of rows returned.

  • 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 a statement

Use the CREATE INDEX statement to create an index.

Examples

Example 1: Create a unique index

To ensure that an indexed column has no duplicate values, you can create a unique index.

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

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

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 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 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 keyword 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 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 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 Cloud in the 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 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-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 Cloud in the 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.

Execute 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 dx_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 return result is as follows:

   +-----------------+-----------------------+-------------+------------+------------+
   | INDEX_NAME      | INDEX_TYPE            | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +-----------------+-----------------------+-------------+------------+------------+
   | DX_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. Spatial indexes are extensively used in the geographic information system (GIS) and in the storage and query of location data. The syntax for creating a spatial index in OceanBase Cloud is different from that in Oracle. You need to specify the SRID of a spatial index column when you create a table in OceanBase Cloud.

When you create a spatial index, take note of the following considerations:

• Before you use the GIS feature, you need to configure GIS metadata in the business tenant.

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

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

3. Query indexes 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 return 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
   

What to do next

After you create an index, you can optimize query performance as needed.