Create indexes|V4.1.0| docs|Distributed Database

Create indexes

Last Updated：2023-08-01 06:02:28 Updated

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.

About indexes

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.

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.

Prerequisites

Before you create indexes, make sure that:

You have logged on to a MySQL tenant of OceanBase Database. For more information, see Connect to the database.

Note

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

You have the INDEX privilege, and the ALTER privilege on the table on which indexes are to be created. For more information about how to view the privileges of the current user, see View user privileges. If you do not have the required privileges, request the administrator to grant the privileges. For more information, see Modify user privileges.
A database has been created. For more information, see Create a database.
A table has been created. For more information, see Create a table.

Considerations

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

You can also create a unique index on an existing table when you use the ALTER TABLE statement to modify the table. This statement allows you to create multiple indexes at a time. You can use either the INDEX or KEY keyword.

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.

Examples:

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

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

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

Use the ALTER TABLE statement to create a unique index on a table.

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

obclient> ALTER TABLE test ADD UNIQUE INDEX idx2 (c2);
Query OK, 0 row affected

Create a non-unique index

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

You can also create a non-unique index on an existing table when you use the ALTER TABLE statement to modify the table. This statement allows you to create multiple indexes at a time. You can use either the INDEX or KEY keyword.

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.

Examples:

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

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

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

Use the ALTER TABLE statement to create a non-unique index on a table.

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

obclient> ALTER TABLE test ADD INDEX idx2 (c1,c2);
Query OK, 0 row affected

Create a local index

A local index is also referred to as a partition index. The keyword for creating a local index is LOCAL. A local index has the same partitioning key and number of partitions as the table, meaning that the index and the table have the same partitioning mechanism. You can create local indexes and local unique indexes. To use a local unique index to ensure data uniqueness, this local unique index must contain the 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 MySQL mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute LOCAL takes effect. In other words, a local index is created by default.
To create a local unique index, make sure that it contains all columns in the partitioning function of the table.

Examples:

Create a local index.

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

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

Create a local unique index.

obclient> CREATE TABLE tbl2_f_rl(col1 INT,col2 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY LIST(col2)
      (PARTITION p0 VALUES LESS THAN(100)
        (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
        (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
      );
Query OK, 0 rows affected

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

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 MySQL mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute LOCAL takes effect. In other words, a local index is created by default.
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 locations of a table and a global index are not necessarily the same, unless you manually include them in the same table group.
Besides a primary key, if you want to ensure global uniqueness for other column combinations, you can create a global unique index.

Examples:

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> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
      PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

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

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> CREATE TABLE tbl2_f_rl(col1 INT,col2 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY LIST(col2)
      (PARTITION p0 VALUES LESS THAN(100)
        (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
        (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
      );
Query OK, 0 rows affected

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

Create a spatial index

OceanBase Database allows you to create spatial indexes by using the syntax for creating regular indexes. However, you must use the SPATIAL keyword.

Note

You must specify SRID and NOT NULL for a column in a SPATIAL index that references a data type. Otherwise, the created index cannot take effect.

You can create a spatial index in the following ways:

To create a SPATIAL index when you create a table by using the CREATE TABLE statement, use the following syntax:

CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL,[column_name data_type,...] SPATIAL INDEX index_name(column_g_name));

To create a SPATIAL index when you modify a table by using the ALTER TABLE statement, use the following syntax:
```
ALTER TABLE table_name ADD SPATIAL INDEX index_name(column_g_name);
```
Note

At present, OceanBase Database does not allow you to modify the SRID attribute of a column by using the ALTER TABLE statement. Therefore, when you create a table, you must properly define the SRID attribute for a spatial column so that the spatial index can take effect.
To use the CREATE INDEX statement to create a SPATIAL index, use the following syntax:
```
CREATE SPATIAL INDEX index_name ON table_name(column_g_name);
```
In a MySQL tenant, index names must be unique in a table. You can use the SHOW INDEX statement to view the indexes on a table.

Parameters

Parameter	Description
index_name	The name of the index.
table_name	The name of the table.
column_name	The name of the column.
data_type	The data type.
column_g_name	The name of the column for the spatial data type.

Examples

Create a table named tbl1_g and create an index named tbl1_g_idx1 on this table.

obclient [test]> CREATE TABLE tbl1_g(id INT,name VARCHAR(18),g GEOMETRY NOT NULL SRID 0,SPATIAL INDEX tbl1_g_idx1(g));
Query OK, 0 rows affected

obclient [test]> SHOW INDEX FROM tbl1_g;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl1_g |          1 | tbl1_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

Create a table named tbl2_g and then create an index named tbl2_g_idx1 when you modify the table.

obclient [test]> CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
Query OK, 0 rows affected

obclient [test]> ALTER TABLE tbl2_g ADD SPATIAL INDEX tbl2_g_idx1(g);
Query OK, 0 rows affected

obclient [test]> SHOW INDEX FROM tbl2_g;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl2_g |          1 | tbl2_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

Create a table named tbl3_g and use the CREATE INDEX statement to create an index named tbl3_g_idx1 on this table.

obclient [test]> CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
Query OK, 0 rows affected (0.060 sec)

obclient [test]> CREATE INDEX tbl3_g_idx1 ON tbl3_g(g);
Query OK, 0 rows affected (0.568 sec)

obclient [test]> SHOW INDEX FROM tbl3_g;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl3_g |          1 | tbl3_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

Create indexes

About indexes

Prerequisites

Note

Considerations

Create a unique index

Create a non-unique index

Create a local index

Create a global index

Create a spatial index

Note

Note

Examples

More information