Create an index

After you create a table, you can create an index on one or more columns in the table to speed up the execution of most SQL queries on the table.

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.

Indexes are classified into unique and non-unique indexes.

• Unique indexes ensure that no two rows of a table have duplicate values in an indexed column. In OceanBase Database, NULL values are also stored in indexes.

• Non-unique indexes allow duplicate values in an indexed column.

For more information, see Unique and non-unique indexes.

Indexes on a partitioned table can be classified into local indexes and global indexes.

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

• A global index has its independent partitioning rules, and one partition of a global index may point to multiple table partitions.

For more information, see Local and global indexes.

Prerequisites

Before you create indexes, make sure that:

• You have logged on to an Oracle tenant of OceanBase Database. For more information about how to connect to a database, see Connection methods.

  Note

  You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.

• You have the INDEX privilege, and the ALTER privilege on the table on which the 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. For more information, see Modify user privileges.

• A table has been created. For more information, see Create a table.

Considerations

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

• 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 a unique index

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

You can use the CREATE UNIQUE INDEX statement to create a unique index on an existing table.

Observe the following requirements when you create unique indexes:

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

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

• An indexed column can have NULL values.

Example 1: Use the CREATE UNIQUE INDEX statement to create a unique index on a table.

obclient [SYS]>CREATE TABLE test(c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER, PRIMARY KEY(c1));
Query OK, 0 rows affected

obclient [SYS]> CREATE UNIQUE INDEX idx1 ON test(c2);
Query OK, 0 rows affected

obclient [SYS]> SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST';
+----------------------------+------------+-------------+------------+------------+
| INDEX_NAME               | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
+----------------------------+------------+-------------+------------+------------+
| TEST_OBPK_1667195501871902 | NORMAL     | SYS         | TEST       | UNIQUE     |
| IDX1                       | NORMAL     | SYS         | TEST       | UNIQUE     |
+----------------------------+------------+-------------+------------+------------+
2 rows in set

Create a non-unique index

You can use the CREATE INDEX statement to create a non-unique index on an existing table.

Observe the following requirements when you create non-unique indexes:

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

• An indexed column can have duplicate values and NULL values.

Example 2: Use the CREATE INDEX statement to create a non-unique index on a table.

obclient [SYS]> CREATE TABLE test(c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER, PRIMARY KEY(c1));
Query OK, 0 rows affected

obclient [SYS]> CREATE INDEX idx1 ON test(c2);
Query OK, 0 rows affected

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.

You can use the CREATE INDEX statement together with the LOCAL keyword to create a local index on an existing table.

You can also use the CREATE UNIQUE INDEX statement together with the LOCAL keyword to create a local unique index on an existing table.

Observe the following requirements when you create local indexes:

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

• In the Oracle mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute GLOBAL takes effect. In other words, a global index is created by default, and the index table contains only one partition.

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

Example 3: Create a local index.

Create a HASH-partitioned table tbl1_h, and then create a local index tbl1_h_idx1 for the table.

obclient [SYS]> CREATE TABLE tbl1_h(col1 NUMBER PRIMARY KEY,col2 NUMBER)
       PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

obclient [SYS]> CREATE INDEX tbl1_h_idx1 ON tbl1_h(col2) LOCAL;
Query OK, 0 rows affected

Example 4: Create a local unique index.

Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a local unique index tbl2_f_rl_idx1 for the table.

obclient [SYS]> CREATE TABLE tbl2_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))
       );
Query OK, 0 rows affected

obclient [SYS]> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1,col2) LOCAL;
Query OK, 0 rows affected

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.

You can use the CREATE INDEX statement together with the GLOBAL keyword to create a global index on an existing table.

You can also use the CREATE UNIQUE INDEX statement together with the GLOBAL keyword to create a global unique index on an existing table.

Observe the following requirements when you create global indexes:

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

• In the Oracle mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute GLOBAL takes effect. In other words, a global index is created by default, and the index table contains 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.

Example 5: Create a global index.

Create a HASH-partitioned table tbl1_h, and then create a RANGE-partitioned global index tbl1_h_idx1 for the table.

obclient [SYS]> CREATE TABLE tbl1_h(col1 NUMBER PRIMARY KEY,col2 NUMBER)
       PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

obclient [SYS]> CREATE INDEX tbl1_h_idx1 ON tbl1_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)
       );
Query OK, 0 rows affected

Example 6: Create a global unique index.

Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a global unique index tbl2_f_rl_idx1 for the table.

obclient [SYS]> CREATE TABLE tbl2_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))
       );
Query OK, 0 rows affected

obclient [SYS]> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1) GLOBAL;
Query OK, 0 rows affected

References

• Query indexes

• Drop an index