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, if a table partition and a global index partition are not in the same physical location, when the TABLE SCAN operator scans a global index, it performs an RPC to fetch 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
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.
Sample code:
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