REMOVE PARTITIONING converts a global index into a regular index for a single table.DROP PARTITION or TRUNCATE PARTITION operations on a partition table that contains a global index, see Partition DDL and the UPDATE GLOBAL INDEXES Clause.user_id, where queries are also frequently performed by order_no. If a LOCAL index is used, a query by order_no fans out to all partitions. Switching to a GSI allows the query to directly locate the partition based on the actual data.CREATE TABLE orders (user_id BIGINT NOT NULL,order_no VARCHAR(32) NOT NULL,amount DECIMAL(10,2),PRIMARY KEY (user_id, order_no),INDEX g_idx_order_no (order_no) GLOBAL -- A global secondary index) PARTITION BY HASH(user_id) PARTITIONS 4;-- During a query, the optimizer uses the GSI to directly locate the partition, eliminating the need for a full partition scan.SELECT user_id, amount FROM orders WHERE order_no = 'NO20240601001';
user_id, but the email and phone fields must be unique across the entire table. A LOCAL unique index can only guarantee uniqueness within a partition and cannot enforce it across partitions. In this case, a global unique index is used to achieve this requirement.CREATE TABLE users (user_id BIGINT NOT NULL,email VARCHAR(128) NOT NULL,phone VARCHAR(20) NOT NULL,PRIMARY KEY (user_id),UNIQUE INDEX g_uk_email (email) GLOBAL, -- Globally unique: the email field is unique across the entire tableUNIQUE INDEX g_uk_phone (phone) GLOBAL -- Globally unique: the phone field is unique across the entire table) PARTITION BY HASH(user_id) PARTITIONS 4;INSERT INTO users (user_id, email, phone) VALUES (1, 'test@example.com', '13800000001');INSERT INTO users (user_id, email, phone) VALUES (2, 'test@example.com', '13800000002');-- ERROR 1062 (23000): Duplicate entry 'test@example.com' for key 'users.g_uk_email'.INSERT INTO users (user_id, email, phone) VALUES (3, 'test2@example.com', '13800000001');-- ERROR 1062 (23000): Duplicate entry '13800000001' for key 'users.g_uk_phone'.
DROP PARTITION or TRUNCATE PARTITION, can cause the original GSI to become inconsistent with the underlying data. TDSQL Boundless uses the UPDATE GLOBAL INDEXES clause to control how the GSI is handled in this situation.Syntax | GSI Handling | Result |
Without UPDATE GLOBAL INDEXES | The GSI is marked as unusable. | The DDL operation executes quickly, but subsequent queries that use the GSI will report errors, requiring manual rebuilding. |
With UPDATE GLOBAL INDEXES | Synchronously rebuilds the GSI during the DDL process. | The DDL operation takes longer, but the GSI remains available throughout. |
order_date and contains the GSI g_idx_order_no to fully demonstrate the differences between the two syntaxes.-- Create a table: Perform RANGE partitioning by year on the order_date column, and create a global secondary index on the order_no columnCREATE TABLE orders (order_no VARCHAR(32) NOT NULL,order_date DATE NOT NULL,user_id BIGINT NOT NULL,amount DECIMAL(10,2),PRIMARY KEY (order_date, order_no),INDEX g_idx_order_no (order_no) GLOBAL) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025),PARTITION p2025 VALUES LESS THAN (2026));-- Insert test data (with order_date being 2023, the data lands in the p2023 partition)INSERT INTO orders (order_no, order_date, user_id, amount) VALUES ('NO20230601001', '2023-06-01', 1001, 99.50);-- Syntax 1: Without the UPDATE GLOBAL INDEXES clause. The partition DDL executes successfully and returns an alarm, but the global secondary indexes will be marked as unavailable.ALTER TABLE orders TRUNCATE PARTITION p2023;-- Query OK, 0 rows affected, 1 warning-- View alarm details by using SHOW WARNINGS:SHOW WARNINGS;-- +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-- | Level | Code | Message |-- +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-- | Warning | 8510 | SQLEngine system error 'Global secondary index 'g_idx_order_no' has been marked as unusable after TRUNCATE PARTITION without UPDATE GLOBAL INDEXES. Use ALTER TABLE ... UPDATE GLOBAL INDEXES to rebuild.' |-- +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-- After an index is marked as unavailable, forcing a query to use that global secondary index will return an index does not exist error.SELECT * FROM orders FORCE INDEX(g_idx_order_no) WHERE order_no = 'NO20230601001';-- ERROR 1176 (42000): Key 'g_idx_order_no' doesn't exist in table 'orders'.-- Recovery method 1: Execute a partition DDL that includes the UPDATE GLOBAL INDEXES clause to rebuild the global secondary indexes while modifying the partitions.ALTER TABLE orders TRUNCATE PARTITION p2024 UPDATE GLOBAL INDEXES;-- Recovery method 2: Delete and then recreate the global secondary index.ALTER TABLE orders DROP INDEX g_idx_order_no;ALTER TABLE orders ADD INDEX g_idx_order_no (order_no) GLOBAL;-- Syntax 2: With the UPDATE GLOBAL INDEXES clause. The global secondary indexes are rebuilt synchronously while the partition DDL is executed, keeping the indexes available throughout the process.ALTER TABLE orders TRUNCATE PARTITION p2025 UPDATE GLOBAL INDEXES;
DROP PARTITION / TRUNCATE PARTITION on tables with GSIs whenever possible. These partition DDL operations can cause global indexes to become inconsistent with the underlying data, either rendering the GSIs unavailable or significantly increasing DDL execution time due to synchronous GSI rebuilds. If your business has frequent requirements for adding or deleting partitions, you should evaluate whether GSIs are truly necessary during the table design phase.UPDATE GLOBAL INDEXES clause to complete the operation in one step and avoid an intermediate availability window.ALTER TABLE DROP / ADD INDEX to rebuild the index.DROP PARTITION and TRUNCATE PARTITION partition operations.Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback