Global index

You can create a global index by specifying the GLOBAL keyword in the creation statement. Unlike a local index, the partitioning of a global index is independent of the partitioning of the table. You can specify the partitioning rules and the number of partitions for a global index. These rules and this number do not have to be the same as those of the table.

Example:

obclient> CREATE TABLE t1(a int PRIMARY KEY, b int, c int) PARTITION BY hash(a) partitions 5;

obclient> CREATE INDEX gkey ON t1(b) GLOBAL PARTITION BY range(b) (
  partition p0 VALUES less than (1), 
  partition p1 VALUES less than (2), 
  partition p2 VALUES less than (3)
);

The partitioning key of a global index is the index key itself. Therefore, when you use a global index, you specify the query condition for the partitioning key of the index. You can use the partitioning rule of the index to perform partition pruning. After you get the index key-value pair, you can use the primary key information in the index table to calculate the location of the partition, thereby quickly locating the partition without scanning all the table partitions. Therefore, when it is impossible to find a partitioning key for a table, you can use a global index to speed up queries.

Example:

obclient> EXPLAIN SELECT /*+index(t1 gkey)*/ * FROM t1 WHERE b=1\G
*************************** 1. row ***************************
Query Plan: ==========================================
|ID|OPERATOR    |NAME    |EST. ROWS|COST |
------------------------------------------
|0 |TABLE LOOKUP|T1      |4950     |38645|
|1 | TABLE SCAN |T1(GKEY)|4950     |1115 |
==========================================

Outputs & filters:
-------------------------------------
  0 - output([T1.A], [T1.B], [T1.C]), filter(nil),
      partitions(p[0-4])
  1 - output([T1.A]), filter(nil),
      access([T1.A]), partitions(p1)

In the preceding query, partition pruning is performed based on the b=1 condition in the WHERE clause to get the global index partition p1. Next, a table scan is performed on the global index to get the primary key. Then, the system uses the table lookup operator to perform a precise scan on the target partition, without scanning all table partitions.

If you do not specify the LOCAL or GLOBAL attribute for an index, OceanBase Database defines this index as a GLOBAL index, and the index has only one partition.

If you do not specify a partitioning key for a table or specify the table to have only one partition, then the physical location of the data in the global index and that of the data in the table are bound together. They are considered as a whole in migration and leader switching. If a table and a global index have the same partition number and boundaries, we recommend that you create a local index instead. The reasons are twofold. First, global indexes cost higher to maintain. Second, the physical storage location of a table and a global index are not necessarily the same, unless you manually include them in the same table group.