Understanding Local and Global Indexes in OceanBase

Author Yang Taotao is a senior database expert with over ten years of experience in MySQL, PostgreSQL, MongoDB and related backup, recovery, SQL tuning, monitoring, and high‑availability architecture design.

OceanBase defines two kinds of indexes based on their relationship with the base table: local (also called local or partition‑aligned) indexes and global indexes.

Local indexes maintain a one‑to‑one relationship with the base table partitions; they inherit the partitioning scheme of the base table, so if the table has ten partitions, each partition has a corresponding local index.

Global indexes have a one‑to‑many or many‑to‑many relationship with the base table and are mainly used for partitioned tables. A non‑partitioned global index can span multiple table partitions, and a partitioned global index also maps multiple partitions of the base table, while each table partition can correspond to multiple global index partitions.

Typical usage scenarios for query filtering

1. Queries that include the partition key should use a local index, which allows direct partition pruning.

Example:

<mysql:5.6.25:ytt> explain select * from p1 where id = 9\G
*************************** 1. row ***************************
Query Plan: ==================================
|ID|OPERATOR|NAME|EST. ROWS|COST|
----------------------------------
|0 |TABLE GET|p1|1|46|
=================================
Outputs & filters:
-------------------------------------
0 - output([p1.id],[p1.r1],[p1.r2]), filter(nil), access([p1.id],[p1.r1],[p1.r2]), partitions(p9)
1 row in set (0.005 sec)

2. Queries that do not contain the partition key may consider a global index, but be aware that global indexes can cause distributed execution.

Two considerations:

If the table’s concurrent writes are low, a global index can be used.

If the table experiences heavy concurrent writes, the decision should be based on workload testing to find a trade‑off point.

Example of a global index on a non‑partition column r2:

<mysql:5.6.25:ytt> explain select * from p1 where r2 = 30\G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR               |NAME          |EST. ROWS|COST|
------------------------------------------------------------
|0 |TABLE LOOKUP            |p1            |101|395|
|1 |DISTRIBUTED TABLE SCAN |p1(idx_r2_global)|101|48|
=============================================================
...

3. Creating a unique index on a column that is neither a primary key nor a partition key.

Option 1: Create a local index, but it must include the full partition key, which adds redundancy and requires query modification.

Attempting to create a local unique index without the partition key results in an error:

<mysql:5.6.25:ytt> create unique index udx_r1 on p1(r1) local;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

Including the partition key resolves the error:

<mysql:5.6.25:ytt> create unique index udx_r1_local on p1(r1,id) local;
Query OK, 0 rows affected (3.012 sec)

Option 2: Create a global unique index, which does not require the partition key and is strongly recommended:

<mysql:5.6.25:ytt> create unique index udx_r1_global on p1(r1) global;
Query OK, 0 rows affected (1.950 sec)

Keywords: #global index# #local index#