Comparison between a local index and a 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, and one partition of a global index may point to multiple table partitions. Because of this, global index data and table data are not necessarily stored together in a distributed environment. This introduces costs caused by remote procedure calls (RPCs) and distributed transactions. For example, when a table partition and a global index partition are not in the same physical location, a TABLE LOOKUP operator 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.
Limitations
When you use global indexes in a distributed environment, distributed transactions and cross-server queries are unavoidable. Global indexes rely on global timestamp service (GTS) to maintain the global consistent snapshot. Therefore, you can use global indexes only 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
obclient> CREATE TABLE t1(a int, b int, PRIMARY KEY(a));
Query OK, 0 rows affected
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
For more information about GTS, see GTS HA.
Tables in OceanBase Database are index organized tables (IOTs). 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, you must ensure that the index contains 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
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
obclient> DROP INDEX idx ON test;
Query OK, 0 rows affected
obclient> CREATE UNIQUE INDEX idx ON test(c2) GLOBAL;
Query OK, 0 rows affected