TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://tidbcloud.com/free-trial
For Global index (for partitioned tables) the syntax is currently not explicit, but implicitly will create an global index if:
the table is partitioned
the index is unique and does not include all the columns in the partitioning expression.
To make it easier for users to see the difference we should make both the index creation/alter and the output from SHOW CREATE TABLE to explicitly show if it is an GLOBAL or LOCAL index.
Definition:
a LOCAL index has the partition id as the key prefix (i.e. is local to the partition, and is maintained together with the partition)
a GLOBAL index has the table id as the key prefix (i.e. covers the whole/global table data, and can enforce uniqueness for the whole table, but needs separate maintenance when doing partitioning management, like removing all index entries matching a dropped/truncated partition).
Reasoning:
GLOBAL or LOCAL index for partitioned tables should be an explicit choice, since before GLOBAL INDEX is supported, only LOCAL will be implicitly chosen.
GLOBAL index will allow removing the limitation that "all unique indexes needs to include all partitioning columns" and it will also require data reorganization (costly index maintenance) for partitioning operations, such as DROP/TRUNCATE PARTITION.
Limitations:
Oracle also allows partitioning a GLOBAL index (in a different way as the table is partitioned), that is not considered here. GLOBAL INDEX means a single non-partitioned index in this context.
Enhancement
For Global index (for partitioned tables) the syntax is currently not explicit, but implicitly will create an global index if:
To make it easier for users to see the difference we should make both the index creation/alter and the output from
SHOW CREATE TABLEto explicitly show if it is an GLOBAL or LOCAL index.Definition:
Reasoning: GLOBAL or LOCAL index for partitioned tables should be an explicit choice, since before GLOBAL INDEX is supported, only LOCAL will be implicitly chosen. GLOBAL index will allow removing the limitation that "all unique indexes needs to include all partitioning columns" and it will also require data reorganization (costly index maintenance) for partitioning operations, such as DROP/TRUNCATE PARTITION.
Limitations: Oracle also allows partitioning a GLOBAL index (in a different way as the table is partitioned), that is not considered here. GLOBAL INDEX means a single non-partitioned index in this context.