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 index by default. To create a local index, you need to add the keyword LOCAL 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_dateon the partitioned table.CREATE INDEX idx_log_date ON t_log_part_by_range_hash(log_date) LOCAL;View the created
idx_log_dateindex.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