Local index
The local index of a partition table is similar to that of a non-partitioned table. The data structure of the index remains the one-to-one relationship with the data structure of the primary table. However, each partition of the primary table has its independent index data structure, since the primary table has been partitioned. In the index data structure, each key only maps to the primary table in its partition, instead of that in other partitions. Therefore, this kind of index is called the local index.
The data structure of this index is also partitioned. Therefore, this kind of index is sometimes called the local partitioned index. The following figure shows the structure of the local index.
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 index
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, you can define the data distribution mode for global indexes, including 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 divided into the following two types:
Global non-partitioned index
These global indexes have no partitions and maintain a single data structure, which is similar to indexes of non-partitioned tables. 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
These of global 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 partition 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 need not create index partitions.
After you learn about the basic knowledge of the local index and global index, you may want to know what scenarios require the global index. Global indexes are typically used in two scenarios:
In addition to the primary key, combinations of other columns must meet the global uniqueness requirement. This business requirement can only be met through globally unique indexes.
Business queries cannot obtain the conditional predicate of the partition key, and the business table has no high-concurrency parallel write-in. To avoid scanning all partitions, you can create a global index based on the query conditions and use a new partition.
Global indexes make it possible to ensure the global uniqueness of data and implement data re-partitioning and 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 greatly 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 to exclude unqualified partitions through the partition pruning function of the database optimizer. This design considers both the query and writing performance to balance the overall system performance.