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

Local indexes of a partitioned table are similar to those of a non-partitioned table. The data structure of indexes is also in a one-to-one correspondence with the data structure of the primary table. However, the primary table has been partitioned, so each partition of the primary table has its own separate index data structure. In the index data structure, each key only maps to the primary table in its partition, instead of that in other partitions. Therefore, this type of index is called the local index.

The data structure of this index is also partitioned. Therefore, this type of index is sometimes called the local partitioned index. The following figure shows the structure of local indexes.

As shown in the preceding figure, OceanBase Database creates range partitions for the employee table based on emp_id and creates local indexes based on emp_name.

Global 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 index

  Similar to indexes of non-partitioned tables, global non-partitioned indexes have no partitions and maintain a single data structure. However, a key of the non-partitioned index may map to different primary table partitions because the primary table is partitioned. This is called the one-to-many relationship. The following figure shows the structure of a global non-partitioned index.

  

• Global partitioned index

  Global partitioned indexes are partitioned in a specified way, such as Hash or Range partitioning, to distribute the index data to different partitions. The partition mode of the partitioned index is completely independent and has nothing to do with the partitions of the primary table. Therefore, each key in each index partition may map to different primary table partitions if the index key has duplicate values. Index partitions maintain a many-to-many relationship with primary table partitions. The following figure shows the structure of a global partitioned index.

  

  As shown in the preceding figure, OceanBase Database creates range partitions for the employee table based on emp_id and creates global partitioned indexes based on emp_name. The key in the same index partition maps to different primary table partitions.

The partition mode of the global index is completely independent of that of the primary table, so it seems that the global index is more like a separate table. Therefore, the global index is also called the index table, which is easier to understand as the opposite of the primary table.

Note

You can also create global partitioned indexes for non-partitioned tables. However, if primary table partitions are unnecessary, you do not need to create index partitions.

We recommend that you use global indexes in the following scenarios:

• In addition to the primary key, combinations of other columns must meet the global uniqueness requirement. This business requirement can only be met by using globally unique indexes.

• Business queries cannot obtain the conditional predicate of the partition key, and the business table does not involve high-concurrency parallel write-in. To avoid scanning all partitions, you can create a global index based on the query conditions. When necessary, you can partition the global index based on a new partition key.

Global indexes make it possible to ensure the global uniqueness of data and implement data re-partitioning and also meet the high requirements of some applications to query data from different dimensions. However, each data write operation may become a cross-IDC distributed transaction, which affects the writing performance in high-concurrency writing scenarios. If a business query can obtain the conditional predicate of the partition key, we still recommend that you create local indexes in OceanBase Database to exclude unqualified partitions by using the partition pruning feature of the database optimizer. This design considers both the query and writing performance to optimize the overall system performance.

Default actions for creating an index

If you do not specify the LOCAL or GLOBAL keyword when you create an index, a global index is created on partitioned tables by default.