Local and global indexes

OceanBase Database supports local and global indexes. This topic describes the concepts of local and global indexes and the default actions for creating an index.

Local indexes

The local index of a partitioned table is similar to the index of a non-partitioned table. The data structure of the index maintains a one-to-one relationship with the data structure of the primary table. However, since the primary table is already partitioned, each partition of the primary table will have its own separate index data structure. For each index data structure, the keys are only mapped to the data within its own partition of the primary table and not to data in other partitions. Therefore, this type of index is referred to as a local index.

From another perspective, the data structure of the index in this pattern is also partitioned, so it is sometimes referred to as a local partitioned index. The structure of a local index is illustrated in the following diagram.

In the above diagram, the employee table is partitioned based on the emp_id column using the range partitioning method. Additionally, a local index is created on the emp_name column.

Global partitioned indexes

Compared with the local index of a partitioned table, the global index of a partitioned table does not maintain the one-to-one relationship with the partitions of the primary table. Instead, it takes the data of all primary table partitions as a whole. In addition, one key of the global index may map to data in multiple primary table partitions if the index key has duplicate values. Furthermore, for a global index, you can define an independent data distribution mode, which can be the non-partitioned mode or partitioned mode. In partitioned mode, the partition structure of global indexes can be the same as or be different from that of the primary table.

Therefore, global indexes are classified into the following two types:

• Global non-partitioned indexes:

  The index data is not partitioned and maintains a single data structure, similar to the indexes of non-partitioned tables. However, since the primary table is partitioned, it is possible for a specific key in the index to map to different partitions of the primary table, resulting in a one-to-many correspondence. The structure of a global non-partitioned index is illustrated in the following diagram.

  

• Global partitioned indexes:

  The index data is partitioned using a specified method, such as Hash or Range partitioning, which disperses the index data across different partitions. However, the partitioning scheme for the index is entirely independent and has no relationship with the partitioning of the primary table. Consequently, for each index partition, a specific key within it may map to different partitions of the primary table (in the case of duplicate index keys), resulting in a many-to-many correspondence between index partitions and primary table partitions. The structure of a global partitioned index is illustrated in the following diagram.

  

  In the above diagram, the employee table is partitioned based on the emp_id column using Range partitioning, while a global partitioned index is created on the emp_name column. It can be observed that within the same index partition, the keys point to different partitions of the primary table.

Global indexes are sometimes referred to as index tables because they are independent of the partitioning scheme used in the primary table. This independence allows global indexes to function more like separate tables, making it easier to understand their relationship with the primary table.

Recommended use cases for global indexes include:

• Business requirements where, in addition to the primary key, a combination of other columns needs to satisfy a strong requirement for global uniqueness. This specific business requirement can only be achieved through a globally unique index.

• Business queries that do not have conditions on the partition key predicates, and the business table does not have high concurrent writes. In order to avoid scanning all partitions, a global index can be constructed based on the query conditions, and if necessary, the global index can be partitioned based on a new partition key.

It is important to note that while global indexes provide global uniqueness and the possibility of data re-partitioning, they come with the cost that each data write may potentially become a distributed transaction across machines. In high-concurrency write scenarios, this can impact the write performance of the system. When business queries can have conditions on the partition key predicates, OceanBase Database still recommends building local indexes. By utilizing the partition pruning function of the database optimizer, it can exclude partitions that do not meet the conditions. This approach can balance both query and write performance, resulting in overall better system performance.

Default actions for creating an index

When creating an index, if you do not specify the LOCAL or GLOBAL keyword, by default, a local index (LOCAL) will be created on a partitioned table.