Indexes on partitioned tables|V4.0.0| docs|Distributed Database

Indexes on partitioned tables

Last Updated：2023-07-21 09:11:01 Updated

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 Database performs partition pruning based on the conditions. Then, OceanBase Database only scans specific partitions. If the SQL statement does not have a sharding key, OceanBase Database scans all partitions.

Background

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. The other type is called a global index that can use an independent partitioning strategy.

We recommend that you use local indexes whenever possible and use global indexes only when necessary. Global indexes can degrade the performance of DML operations because they may cause DML operations to become distributed transactions.

Generally, an index is created as a global local by default. To create a index index, you need to add the keyword GLOBAL to the end of the statement.

Syntax

CREATE [UNIQUE] INDEX index_name
     ON table_name (index_col_name,...)
      [index_type] [index_options]
      [partition_option]

index_type:
      USING BTREE

index_options:
      index_option [index_option...]

index_option:
    GLOBAL | LOCAL
    | COMMENT 'string'
    | COMPRESSION [=] {NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}
    | BLOCK_SIZE [=] size
    | STORING(column_name_list)
    | VISIBLE | INVISIBLE

index_col_name:
    column_name [(length)] [ASC | DESC]

column_name_list:
    column_name [, column_name...]

Parameters

Parameter	Description
index_name	The name of the index to be created.
table_name	The name of the table to which the index belongs.
index_col_name	The column name of the index. You can add ASC (ascending order) to the end of each column name. DESC (descending order) is not supported. By default, the columns are sorted in ascending order. Index-based sorting method: Data is first sorted by the values in the first column of `index_col_name` and by the values in the next column for the records with the same values in the first column.
column_name	The column name used to create the index.
length	For a string column, you can use the `col_name(length)` syntax to extract part of the string for creating an index. Supported data types: `CHAR`, `VARCHAR`, `BINARY`, and `VARBINARY`.
index_type	The index type. Valid value: `USING BTREE`, which indicates that B-tree indexes can be used.
UNIQUE	Specifies the index to a unique index.
index_option	The index options. Multiple `index options` are separated with spaces.
GLOBAL \| LOCAL	Specifies whether the index is a global index or a local index. The default value is `LOCAL`.
COMMENT	The comment.
COMPRESSION	The compression algorithm.
BLOCK_SIZE	The microblock size.
STORING	Specifies to store copies of some columns in the index table to improve the query performance of the system.

Examples

Create a local index on a partitioned table.

Create a table named t_log_part_by_range_hash.

CREATE TABLE t_log_part_by_range_hash (log_date int primary key) ;

Create a local index named idx_log_date on the partitioned table.

CREATE INDEX idx_log_date ON t_log_part_by_range_hash(log_date) ;

View the created idx_log_date index.

obclient> SHOW INDEX FROM t_log_part_by_range_hash;
+--------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table                    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| t_log_part_by_range_hash |          0 | PRIMARY      |            1 | log_date    | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| t_log_part_by_range_hash |          1 | idx_log_date |            1 | log_date    | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
+--------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
2 rows in set