Create an index

This topic describes how to create an index with some 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 indexes, 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 database. For more information about how to create a database, see Create a table.

• You have the INDEX privilege and the ALTER privilege for the table on which the index is to be added. For information about how to view your privileges, see View user privileges. If you do not have the privilege, contact the administrator. For more information, see Modify user 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 partition key.

Considerations

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

Use statements to create indexes

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

   CREATE UNIQUE INDEX idx_test_tbl1_col2 ON test_tbl1(col2);
   

3. View the index information for table test_tbl1.

   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

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

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

   CREATE INDEX idx_test_tbl2_col2 ON test_tbl2(col2);
   

3. View the index information for table test_tbl2.

   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: Creating a local index

A local index is created for data on a single partition. Therefore, the index key values of a local index have a one-to-one correspondence with the data in the table. That is, a partition on a local index always corresponds to a table partition, and they have the same partition rules. Therefore, for a local unique index, it can only ensure uniqueness within the partition, and cannot guarantee the global uniqueness of table data. The keyword for creating a local index is LOCAL.

If you want to use a local unique index to enforce data uniqueness, make sure that the local unique index includes the table partition key.

Use the following SQL statements to create a sub-partitioned table named tbl3_f_rl and to create a local unique index on the col1 and col2 columns in table tbl3_f_rl.

1. Create a non-templated Range + List partitioned table 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 unique index named idx_tbl3_f_rl_col1_col2 on the col1 and col2 columns in table tbl3_f_rl.

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

3. View the index information for table tbl3_f_rl.

   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. Compared with local indexes, the most significant feature of global indexes is that the partitioning rules of a global index are independent of the table partitions. Global indexes allow you to specify their own partitioning rules and the number of partitions, which do not necessarily need to be consistent with the table partitioning rules.

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

• The partitioning rules of global indexes do not necessarily have to be consistent with the table partitioning rules.

• If the partitioning rules of the global index are the same as those of the primary table and the number of partitions is the same, we recommend that you create a local index. This is because, on the one hand, the maintenance cost of global indexes is higher, and on the other hand, global indexes cannot guarantee the same physical location as the main table partitions, unless they are specified in the same table group.

Use the following SQL statements to create a partitioned table named tbl4_h and to create a global index on the col2 column in table tbl4_h.

1. Create a Hash partitioned table tbl4_h.

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

2. Create an index named idx_tbl4_h_col2 on the col2 column in table tbl4_h, where the index is a Range partition index based on the col2 column.

   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 for table tbl4_h.

   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

An index created based on the results of calculations on one or more columns in a table is called a function-based index. A function-based index is an optimization technique that allows for the quick location of matching function values during queries, thus avoiding duplicate calculations and improving query efficiency.

In the Oracle mode of OceanBase Database, there are restrictions on the expressions for function-based indexes. Some system functions are prohibited from being used in function-based indexes. For the specific list of allowed and disallowed system functions in function-based indexes, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

Use the following SQL statements to create a table named test_tbl5 and to create a function index on the id column.

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 table test_tbl5.

   CREATE INDEX dx_test_tbl5_id ON test_tbl5 ((id+1));
   

3. Execute the following statement to view the function index created:

   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 |
   +-----------------+-----------------------+-------------+------------+------------+
   | DX_TEST_TBL5_ID | FUNCTION-BASED NORMAL | SYS         | TEST_TBL5  | NONUNIQUE  |
   +-----------------+-----------------------+-------------+------------+------------+
   1 row in set
   

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 view indexes, see Query indexes.

• For more information about how to manage indexes, see DROP INDEX and Drop an index.

• For more information about the system functions supported by function-based indexes, see System functions supported for function-based indexes.