Global indexes

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.

Create a global index

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 quantity 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 locations of a table and a global index are not necessarily the same.

In the Oracle mode of OceanBase Database, if the keyword of an index is not specified, GLOBAL is used as the keyword by default. In this case, the index is created as a global index, and the index table has only one partition. Here is an example:

obclient> CREATE TABLE tbl1(col1 INT,col2 INT)
       PARTITION BY HASH(col2) PARTITIONS 5;
Query OK, 0 rows affected

obclient> CREATE INDEX idx1 ON tbl1(col1);
Query OK, 0 rows affected

Create a global index

Create a HASH-partitioned table tbl1_h, and then create a RANGE-partitioned global 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) GLOBAL
       PARTITION BY RANGE(col2)
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200),
        PARTITION p2 VALUES LESS THAN(300)
       );
Query OK, 0 rows affected

Create a global unique index

Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a global 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(1,3),
          SUBPARTITION sp1 VALUES(4,6),
          SUBPARTITION sp2 VALUES(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES(1,3),
          SUBPARTITION sp4 VALUES(4,6),
          SUBPARTITION sp5 VALUES(7,9))
       );
Query OK, 0 rows affected

obclient> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1) GLOBAL;
Query OK, 0 rows affected

Partition pruning

The partitioning key of a global index is the index key itself. Therefore, when you use a global index, you must 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 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.

Here is an example:

obclient> EXPLAIN SELECT /*+index(t1 gkey)*/ * FROM t1 WHERE b=1;
+---------------------------------------------------------------+
| Query Plan                                                    |
+---------------------------------------------------------------+
| ==========================================================    |
| |ID|OPERATOR              |NAME    |EST.ROWS|EST.TIME(us)|    |
| ----------------------------------------------------------    |
| |0 |DISTRIBUTED TABLE SCAN|t1(GKEY)|4950    |38645       |    |
| ==========================================================    |
| Outputs & filters:                                            |
| -------------------------------------                         |
|   0 - output([t1.A], [t1.B], [t1.C]), filter(nil), rowset=256 |
|       access([t1.A], [t1.B], [t1.C]), partitions(p0)          |
|       is_index_back=true, is_global_index=true,               |
|       range_key([t1.B], [t1.A]), range(1,MIN ; 1,MAX),        |
|       range_cond([t1.B = 1])                                  |
+---------------------------------------------------------------+

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 table to get the primary key. Then, the system uses the primary key to perform a precise scan on the target partition, without scanning all table partitions.