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.
Overview
A local index is created on data 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 local unique index is only guaranteed to be unique in a partition. Its uniqueness within the table is not guaranteed.
To use a local unique index as a unique constraint on a table, the local unique index must contain a partitioning key of the table.
Notice
In the MySQL mode of OceanBase Database, if the keyword of an index is not specified,
LOCALis used as the keyword by default. In this case, the index is created as a local index.A unique index must contain all columns in the table partitioning function.
Examples
Create a local index
Create a HASH-partitioned table tbl1_h, and then create a local index tbl1_h_idx1 for the table.
obclient> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected
obclient> CREATE INDEX tbl1_h_idx1 ON tbl1_h(col2) LOCAL;
Query OK, 0 rows affected
Create a local unique index
Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a local unique index tbl2_f_rl_idx1 for the table.
obclient> CREATE TABLE tbl2_f_rl(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST(col2)
(PARTITION p0 VALUES LESS THAN(100)
(SUBPARTITION sp0 VALUES IN(1,3),
SUBPARTITION sp1 VALUES IN(4,6),
SUBPARTITION sp2 VALUES IN(7,9)),
PARTITION p1 VALUES LESS THAN(200)
(SUBPARTITION sp3 VALUES IN(1,3),
SUBPARTITION sp4 VALUES IN(4,6),
SUBPARTITION sp5 VALUES IN(7,9))
);
Query OK, 0 rows affected
obclient> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1,col2) LOCAL;
Query OK, 0 rows affected
Partition pruning
In OceanBase Database, 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 accessed by a query.
The following is an example of partitioning pruning with a partitioning key specified in the query condition:
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
If no partitioning key is specified in a query, the local index cannot use partition pruning. In this case, all partitions are scanned, which increases the cost of the scan.
If no partitioning key is specified in the query condition, partition pruning cannot be used. Here is an example:
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