Use an index

Local index vs global index

A table and a local index on the table are partitioned in the same way. A global index has its independent partitioning rules. Therefore, one partition of a global index may point to multiple table partitions. Because of this, global index data and table data aren't necessarily stored together in a distributed environment. This introduces costs caused by remote procedure calls (RPC) and distributed transactions. For example, when a table partition and a global index partition are not in the same physical location, a TABLE LOOKUP operation includes an RPC that fetches table data from a remote server. Therefore, global indexes cost higher to maintain. We recommend that you design the table partitioning rules carefully, choose a reasonable partitioning key that satisfies as many query conditions as possible, and avoid using global indexes.

Limits

When you use global indexes in a distributed environment, distributed transactions and remote queries are unavoidable. Global indexes rely on global timestamp service (GTS) to maintain the global consistent snapshot. Therefore, you can only use global indexes when GTS is enabled. You cannot create a global index if GTS is disabled.

Example:

obclient> SET GLOBAL ob_timestamp_service=LTS;
Query OK, 0 rows affected (0.06 sec)

obclient> CREATE TABLE t1(a int, b int, PRIMARY KEY(a));
Query OK, 0 rows affected (0.17 sec)

obclient> CREATE INDEX gkey ON t1(b) PARTITION BY range(b) (PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN(3)); 
ERROR 1235 (0A000): create global index when GTS is off not supported

Tables in OceanBase Database are index organized tables (IOT). For partitioned tables, to ensure that a query with a specified primary key can quickly locate a partition, the partitioning key must be a subset of the primary key. To create a local partitioned unique index on a partitioned table, the index must contain the partitioning key of the table. This constraint does not apply to global partitioned unique indexes.

Example:

obclient> CREATE TABLE test(pk int,c2 int ,c3 int, PRIMARY KEY(pk)) PARTITION BY hash(pk) partitions 5;
Query OK, 0 rows affected (0.20 sec)
obclient> CREATE UNIQUE INDEX idx ON test(c2) LOCAL;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

obclient> CREATE UNIQUE INDEX idx ON test(c2, pk) LOCAL;
Query OK, 0 rows affected (5.34 sec)
obclient> DROP INDEX idx ON test;
Query OK, 0 rows affected (0.02 sec)
obclient> CREATE UNIQUE INDEX idx ON test(c2) GLOBAL;
Query OK, 0 rows affected (17.47 sec)