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.
Overview
An index, also known as a secondary index, is an optional structure. OceanBase Database adopts 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 create indexes for some fields based on your business requirements to speed up queries for the fields.
Indexes can be classified into unique indexes and non-unique indexes.
A unique index ensures that the indexed column does not contain two rows with identical values. In OceanBase Database, null values are also stored in indexes.
A column with a non-unique index may contain identical values.
In a partitioned table, indexes can also 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.
The partitioning of a global index is independent of the partitioning of the table. Therefore, one partition of a global index may point to multiple table partitions.
Prerequisites
Before you create an index, make sure that:
You have logged on to an Oracle tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Connect to OceanBase Database.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the mode of the tenant to which you have logged on.You have the
INDEXprivilege and theALTERprivilege on the table where an index is to be created. For more information about how to view your privileges, see View user privilege. 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 about how to create a table, see Create a table.
Suggestions on index creation
Create indexes on fields that are frequently used for queries, but do not create excess indexes on tables that are frequently updated.
Do not use indexes for tables with small data volumes. This is because the time consumed in querying all data may be shorter than that in traversing the indexes. In this case, indexes may not be useful.
Do not create indexes when the modification performance is far greater than the query performance.
Create efficient indexes:
Include all the necessary columns required for queries. The more relevant columns included in the index, the better it can minimize the number of rows returned.
Place equal conditions at the beginning of the index as this is more effective when searching through large amounts of data.
Place filtering and sorting operations at the start of the index to optimize the search process.
Create a unique index
To avoid duplicate values in an indexed column, create a unique index on the column.
You can use the CREATE UNIQUE INDEX statement to create a unique index on an existing table based on the following requirements.
Observe the following requirements when you create unique indexes:
In OceanBase Database, index names must be unique in the table.
Multiple unique indexes with unique column values are allowed.
Null values are allowed in indexed columns.
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 based on the following requirements.
Observe the following requirements when you create non-unique indexes:
In OceanBase Database, index names must be unique in the table.
Indexed columns can have duplicate values, including 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.
Use the CREATE INDEX statement and the LOCAL keyword to create a local index on an existing table based on the following requirements.
Use the CREATE UNIQUE INDEX statement and the LOCAL keyword to create a local unique index on an existing table based on the following requirements.
Observe the following requirements when you create local indexes:
In OceanBase Database, index names must be unique in the table.
If you do not specify the LOCAL or GLOBAL attribute for an index, OceanBase Database in Oracle mode defines this index as a
GLOBALindex, and the index has 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 named tbl1_h, and then create a local index named 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 named tbl2_f_rl without using a template, and then create a local unique index named 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
Use the GLOBAL keyword to create a global index. 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.
Use the CREATE INDEX statement and the GLOBAL keyword to create a global index on an existing table based on the following requirements.
Use the CREATE UNIQUE INDEX statement and the GLOBAL keyword to create a global unique index on an existing table based on the following requirements.
Observe the following requirements when you create global indexes:
In OceanBase Database, index names must be unique in the table.
If you do not specify the LOCAL or GLOBAL attribute for an index, OceanBase Database in Oracle mode defines this index as a
GLOBALindex, and the index has only one partition.The partitioning rules of a global index do not need to be consistent with those of the table.
If a global index has the same partitioning rules and number of partitions as the table, we recommend that you create a local index instead. The reasons are twofold. First, global indexes cost higher to maintain. Second, the physical storage location of a table and that of 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 named tbl1_h, and then create a RANGE-partitioned global index named 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 named tbl2_f_rl without using a template, and then create a global unique index named 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