Partitioned indexes

As data volumes in a database table increase, you can partition the table to distribute the data across the cluster to improve overall service capability. You can also create indexes on the table to speed up queries. Local indexes, global indexes, and unique indexes are introduced in this topic.

Index types

OceanBase Database supports the following index types:

• Local partitioned index: A local partitioned index is created by specifying the LOCAL keyword when creating an index. A local partitioned index does not require a partitioning rule. It has the same partition attribute as the primary table and is affected by any partition operations on the primary table.

• Global partitioned index: A global partitioned index is created by specifying the GLOBAL keyword when creating an index. A global partitioned index can be partitioned based on a specified partitioning rule.

• Unique index: A unique index is created by specifying the UNIQUE keyword. The values of the index key are unique.

• Prefix index: A prefix index is an index where the partitioning key of the index table is the prefix of the index key. For example, an index table idx is created based on columns c1, c2, and c3. If the partitioning key of the index table is c1, the index is a prefix index. If the partitioning key of the index table is c2 or any other column, the index is a non-prefix index.

• Non-prefix index: A non-prefix index is an index that is not a prefix index.

Local indexes

Local indexes are further divided into local prefix indexes and local non-prefix indexes based on the partitioning key.

Local prefix indexes

A local prefix index is a unique or non-unique index where the partitioning key of the index is the prefix of the index key. Local prefix indexes can be partitioned based on a specified partitioning rule.

A query that specifies the index key can uniquely locate to one index partition based on a local prefix index. This is suitable for scenarios with a small result set but requiring partition pruning.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned based on the c1 column. Based on the characteristics of the local index, the index table and the primary table have the same partitioning rule. Therefore, the idx index is also partitioned based on the c1 column. As defined, this index is a local prefix index. When a query specifies the index key, the system can uniquely locate to one index partition based on the partitioning key c1, greatly reducing the number of index partitions to be accessed.

Local non-prefix indexes

A local non-prefix index is an index that is not a local prefix index. This means that the partitioning key of the index is not the prefix of the index key, or the index does not contain the partitioning key.

If the partitioning key is not a subset of the index key, a local non-prefix index cannot be a unique index. A query that specifies the index key cannot uniquely locate to index partitions based on a local non-prefix index. Instead, it needs to access all index partitions. Therefore, it is suitable for scenarios with a large amount of data and high concurrency.

For example, a local index idx(c1,c2,c3) is created on table A. The primary table is partitioned based on the c4 column. Based on the definition, this index is a local non-prefix index. When a query specifies the index key, the system cannot uniquely locate to index partitions based on the partitioning key. Therefore, it needs to access all index partitions to return the query result. In this scenario, concurrency can play an important role.

Global indexes

A global index has its own independent partition definition, which does not necessarily need to be the same as that of the primary table. Partitions of a global index can be split or merged. Generally, if the partitioning rule of the primary table is the same as that of the global index, unique global indexes other than unique non-prefix indexes are recommended to be defined as local indexes. This is because maintaining a global index incurs higher partition management and maintenance costs than maintaining a local index, and global indexes and local indexes have the same query costs and benefits when they have the same partitioning rule.

Global prefix indexes

A global prefix index is a global index where the first column is the partitioning key of the table. In other words, a global prefix index is created by specifying the GLOBAL keyword and using the primary key as the partitioning key.

A global prefix index can be a unique index or a non-unique index.

A global prefix index has meaning only when the RANGE partitioning method is used, but not when the HASH partitioning method is used. If the HASH partitioning method is chosen, the query pattern is generally a point query using the index key. In this case, whether the index is a prefix index or not does not matter. The index keys in the same partitions can be uniquely maintained, regardless of whether the index keys are prefixes of each other. If the RANGE partitioning method is used, the system can prune partitions. This means that the system does not need to search all partitions for the query result.

Global non-prefix indexes

OceanBase Database does not support global non-prefix indexes. Therefore, global non-prefix indexes have no significance for query optimization.

For example, a global index idx(c1,c2) is created on table A. The index table idx is partitioned based on the c2 column. In this case, the idx index is a global non-prefix index. In this setup, partition pruning can be achieved only when all index keys are specified in a query. Otherwise, the system needs to scan all index partitions. Therefore, there is no reason to use c2 for partitioning instead of c1. Moreover, the system cannot use prefix filtering with the c2 index.

Unique indexes

A unique index can be a global index or a local index.

A unique index must meet the following condition when it is a local index: the index key must cover the partitioning key. For example, a unique index idx(c1,c2,c3) is created on table A. The index table idx is partitioned based on the (c1,c2) column pair. This setup ensures that the same (c1,c2) value can enter only one partition. The unique index is maintained within each partition. If the index table idx is partitioned based on the (c2,c4) column pair, the index does not contain the partitioning key. In this case, a unique index cannot be maintained between partitions, and a local unique index cannot be created.

Index creation strategies

When you create an index, consider your query patterns, index management, performance, and availability requirements to choose the most suitable index strategy for your business.

• If you need a unique index and the index key covers all partitioning keys, define the index as a local index. Otherwise, define the index as a global index.

• If the partitioning keys of the primary table are a subset of the index keys, define the index as a local index.

• If the partitioning attribute of the primary table is the same as that of the index, we recommend that you define the index as a local index.

• If you are more concerned about the costs of index partition management and the partitions of the primary table are constantly being split, we recommend that you do not create a global index. This is because split partitions of the primary table cause significant changes in the global index, making the global index difficult to restore and potentially unavailable.

• If your queries always specify all index partitioning keys, you can create an index on other columns without partitioning to reduce the maintenance and storage costs; prefix indexes are suitable for scenarios with small data volumes where partition pruning is needed, and non-prefix indexes generally suit scenarios with large data volumes where parallel queries are common.

Partition selection strategies

When you create a partitioned index, if a single index table contains a large volume of data, you need to divide the data into several partitions to facilitate parallel execution and load balancing.

• If your queries are mostly single-point queries by index key and take into account the number of partitions to be accessed and the concurrency of access, the query costs of HASH partitioning and RANGE partitioning are similar. However, if data has hot spots, HASH partitioning can better avoid hot spots.

• If your queries are mostly range queries by index key, RANGE partitioning allows access to fewer partitions than HASH partitioning, but HASH partitioning can better utilize concurrent queries, especially when the result set is large. In other words, HASH partitioning has a greater advantage over RANGE partitioning in scenarios with large data volumes.

Query-time index selection strategies

• If you want to perform partition pruning, prefer prefix indexes. Partition pruning can be maximally achieved by specifying the partition prefix in the query filter condition, thereby reducing the amount of index partition data that needs to be read.

• If you prioritize throughput and a large amount of data is to be accessed, prefer non-prefix indexes. Non-prefix indexes allow parallel access to partitions for range queries on partitioning keys. Local non-prefix indexes can concurrently access all partitions, whereas local prefix indexes prune partitions. As a result, a few partitions need to process a large amount of data, which can result in longer response times compared to parallel queries.