The query performance of a partitioned table depends on the conditions in the SQL statement. If an SQL statement with a sharding key is executed, OceanBase performs partition pruning based on the conditions. Then, OceanBase only scans specific partitions. If the SQL statement does not have a sharding key, OceanBase scans all partitions.
You can also create indexes to improve the query performance of partitioned tables. Indexes on partitioned tables can either be partitioned or non-partitioned.
A non-partitioned index of a partition is a global index. It is a single index object that refers to all rows in the partitioned table. Non-partitioned indexes are always created as independent index objects in a single table space.
Partitioned indexes are classified into two types based on their partitioning strategies. The first type is called a local index that is partitioned in the same manner as the partitioned table. Each partition of a local index corresponds to one partition of the partitioned table.
We recommend that you use local indexes whenever possible and use global indexes only when necessary. Global indexes can degrade the performance of data manipulation language (DML) operations because they may cause DML operations to become distributed transactions.
Generally, an index is created as a global index by default. To create a local index, you need to add the keyword LOCAL to the end of the statement.
Example: Creating a local index on a partitioned table
obclient> CREATE INDEX idx_log_date ON t_log_part_by_range_hash(log_date) LOCAL;
Query OK, 0 rows affected (5.16 sec)