A local index is also called a local partitioned index. It is created by using the LOCAL keyword. A local index has the same partitioning key and number of partitions as the table, meaning that the index and the table have the same partitioning mechanism.
The following statement creates the local index idx for the partitioned table t1.
obclient> CREATE TABLE t1(a int primary key, b int) PARTITION BY hash(a) partitions 5;
obclient> CREATE INDEX idx ON t1(b) local;
A local index is created on a column in a single partition. The key-value pairs of the index and the data in the table are in a one-to-one match. Each index partition maps to one table partition. They share the same partitioning rules. Therefore, a unique local index is only guaranteed to be unique in a partition. Its uniqueness within the table is not guaranteed. To use a unique local index as a unique constraint on a table, the unique local index must contain a partitioning key of the table.
For example, the following statement creates the unique local index uk on the partitioned table t2.
obclient> CREATE TABLE t2(a int primary key, b int) PARTITION BY hash(a) partitions 5;
obclient> CREATE UNIQUE INDEX uk ON t2(b) LOCAL;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
obclient> CREATE UNIQUE INDEX uk2 on t2(b, a) local;
Query OK, 0 rows affected (5.32 sec)
In OceanBase, local indexes also support partition pruning. One prerequisite for partition pruning is that the query condition must contain a partitioning key. This increases query efficiency by reducing the number of partitions read by a query. For example, the following query contains a partitioning key in its WHERE clause:
obclient> EXPLAIN SELECT /*+index(t1 idx)*/ b FROM t1 WHERE b=1 AND a=1\G
*************************** 1. row ***************************
Query Plan: =====================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-------------------------------------
|0 |TABLE GET|t1(idx)|1 |52 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.b]), filter(nil),
access([t1.b]), partitions(p1)
1 row in set (0.01 sec)
If no partitioning key is specified in a query, the local index cannot use partition pruning. In this case, all the partitions are scanned, which increases the cost of the scan. For example, in the following statement, no partitioning key is specified:
obclient> EXPLAIN SELECT /*+index(t1 idx)*/ b FROM t1 WHERE b=1\G
*************************** 1. row ***************************
Query Plan: ====================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------------
|0 |EXCHANGE IN DISTR | |4950 |3551|
|1 | EXCHANGE OUT DISTR |:EX10000|4950 |3083|
|2 | PX PARTITION ITERATOR| |4950 |3083|
|3 | TABLE SCAN |t1(idx) |4950 |3083|
====================================================
Outputs & filters:
-------------------------------------
0 - output([t1.b]), filter(nil)
1 - output([t1.b]), filter(nil), dop=1
2 - output([t1.b]), filter(nil)
3 - output([t1.b]), filter(nil),
access([t1.b]), partitions(p[0-4])
1 row in set (0.01 sec)
