Feature Overview
Creating global secondary indexes is supported for partitioned tables.
Supported Versions
The kernel version should be 3.1.15 or later for TXSQL 8.0.
Applicable Scenarios
Global secondary indexes are created in partitioned tables to improve the query efficiency and performance for the partitioned tables.
Notes
DDL use limits (the following are DDL operations not supported in partition_option according to the official documentation): DISCARD PARTITION and IMPORT PARTITION: They are not supported because global indexes and partitions are not in the same table space.
COALESCE PARTITION: For tables without a primary key, performing this operation will cause duplicate records to occur in global indexes.
REORGANIZE PARTITION: It may cause duplicate data to occur in global indexes.
EXCHANGE PARTITION: Global indexes and partitions are not in the same table space, so the global indexes cannot exchange data.
REBUILD PARTITION: Duplicate records may occur in partitioned tables without a primary key.
REPAIR PARTITION: It may cause data loss in global indexes.
Global indexes cannot be used as a primary key or implicitly converted to a primary key (when there is no primary key, no unique global index can be created).
Unique global indexes must contain all partition fields.
Global indexes do not currently support partition-level DDL operations that require copying data.
Global indexes do not support compressing tables (modifying KEY_BLOCK_SIZE) or transparent pages.
Global indexes do not support non-partitioned tables. When a global index is created on a non-partitioned table, it will be automatically converted to a common index.
Hash partitions with global indexes do not support the Add and Coalesce operations.
For partitioned tables with global indexes, the syntax alter table truncate partition with global index should be used in partition truncating.
AHI of global indexes is disabled by default.
It is recommended to access global indexes with the simple select method. Special usage is not advised.
Use Instructions
Creating a Global Index Table
create table t1 (
a bigint unsigned not null PRIMARY KEY,
b varchar(16) not null,
pad varchar(128) not null,
key key_b(b) global -- Add the `global` keyword to specify a global index during table creation.
)
PARTITION BY RANGE(a) (
PARTITION p0 VALUES LESS THAN (10000000),
PARTITION p1 VALUES LESS THAN (20000000),
PARTITION p2 VALUES LESS THAN (30000000),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
Creating a Global Index in an Existing Table
create index key_b on t1(b) global; -- Add the `global` keyword to specify a global index during index creation.
alter table t1 add index key_b(b) global;