Understanding Global vs Local Indexes in OceanBase and Their Impact on Query Performance

The article begins by describing a migration scenario where an Oracle table cannot use row movement, requiring special handling with the dbcat tool and resulting in two possible index strategies: converting the primary key to a global unique index via OMS or adding the partition key to the primary key as a local index via dbcat .

It then clarifies the concepts of global and local indexes, noting that only partitioned tables have this distinction; MySQL InnoDB supports only local indexes, while Oracle supports global indexes. Global indexes store a single index structure for all partitions, whereas local indexes maintain separate structures per partition.

The performance impact is illustrated with a test case: a query on the driven table b using the primary key (a local index) takes 90 seconds, while forcing the use of a global unique index with the hint /*+ index(b UIDX_DATA_ID2) */ reduces execution time to 5 seconds. Execution plans show that the local index forces scans across all 130 partitions, whereas the global index accesses only one partition.

Further analysis of the execution plans demonstrates that the global index’s leaf nodes contain the primary key, enabling partition pruning during the lookup, while the plan output for the local index incorrectly lists many partitions. The article emphasizes that in a non‑distributed OceanBase cluster, global indexes avoid distributed transaction overhead and provide the best performance when the query cannot be partition‑pruned.

Finally, the article concludes that using a global unique index is the most efficient solution for queries that cannot benefit from partition pruning, especially in non‑distributed OceanBase deployments, and provides a reference to the official OceanBase documentation on global and local indexes.