Comparison between a local index and a global index
Compared with local indexes, global indexes have their own partitioning rules. This means that the index values of a partition in the index table may correspond to data in multiple partitions of the primary table. In a distributed environment, due to potential differences in partitioning rules between indexes and the primary table, there is no guarantee that index data and primary table data will always be stored in the same location. Consequently, this introduces the costs of read/write RPC operations and distributed transactions.
For example, when the partitions of the primary table and the global index are not located in the same physical location, executing a TABLE SCAN operator on the global index table will require an RPC operation to fetch the primary table data from a remote server. Therefore, global indexes require more effort to maintain compared with local indexes. It is crucial to carefully evaluate the partitioning rules of the primary table and make wise choices for the partition key. Whenever possible, ensure that the partition key of the primary table covers more query conditions, minimizing the need for 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.
Here is an 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