PolarDB:Rebuild a clustered columnar index

Prerequisites

• A columnar index has been created.

• The rebuild process is performed online and does not acquire locks. It will not interfere with existing workloads on either the columnar index or the underlying row-based table.

Important notes

Your instance must be running version 5.4.20-20250714 or later.

When to rebuild a CCI

Rebuilding a CCI is necessary when you need to adjust its design to optimize performance or adapt to business changes. Common scenarios include:

• To optimize query performance: During initial testing, you may find that an index's performance does not meet expectations. Rebuilding the CCI with a different sort key or partition key that better matches your data access patterns can significantly improve query speed.

• To adapt to schema or business logic changes: As your business evolves, you may need to add, remove, or modify columns in your tables. If the associated SQL queries also change, the existing CCI may no longer be optimal. Rebuilding the index ensures it remains aligned with your new schema and query requirements.

• To adjust to data growth and distribution: As data volume grows, the original partitioning strategy may become inefficient. You may need to rebuild the index to change the partition key, adjust the number of partitions, or partition databases or tables by a time-based column to better manage large datasets.

• To support new features and upgrades: When the database introduces significant updates to the CCI feature, you may need to rebuild existing indexes to take advantage of the new capabilities and ensure compatibility.

Syntax

1. Set the MAX_CCI_COUNT variable

   The rebuild process temporarily creates a new CCI before dropping the legacy one. To allow this, you must first increase the maximum number of CCIs allowed per table to at least 2.

   SET GLOBAL MAX_CCI_COUNT = 2;

   Note

   If the target table already contains multiple CCIs, set this value higher than the total number of existing indexes.

2. Rebuild the index

   You can rebuild an index using either a synchronous or asynchronous operation.

   Synchronous rebuild

   Use the ALTER TABLE REBUILD syntax to perform a synchronous rebuild. This operation blocks the session until the rebuild is complete. See Create and use a CCI for details.

   ALTER TABLE tbl_name REBUILD ClUSTERED COLUMNAR INDEX index_name [CCI DEFINITION]; 

   Note

   • The specified table (tbl_name) and CCI (index_name) must exist. Otherwise, an error will be returned.

   • You cannot rebuild an archived CCI.

   Asynchronous rebuild (Recommended for large tables)

   For large tables where the rebuild process may take a long time, it is recommended to execute the operation asynchronously.

   ALTER TABLE tbl_name REBUILD ClUSTERED COLUMNAR INDEX index_name [CCI DEFINITION] ASYNC=TRUE;

Examples

This example demonstrates how to change the sort key and the number of partitions for an existing CCI.

1. Create a table with an initial CCI.

   The sort key and partition key are both set to id.

   CREATE TABLE `t_order` (
       `id` bigint(11) NOT NULL AUTO_INCREMENT,
       `order_id` varchar(20) DEFAULT NULL,
       `buyer_id` varchar(20) DEFAULT NULL,
       `seller_id` varchar(20) DEFAULT NULL,
       `order_snapshot` longtext,
       CLUSTERED COLUMNAR INDEX `cci_test` (`id`) PARTITION BY KEY(`id`) PARTITIONS 4,
       PRIMARY KEY (`id`)
   ) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 2
   PARTITION BY KEY(`id`)
   PARTITIONS 16;

2. Rebuild the CCI with a new definition.

   The sort key is changed to seller_id, and the partition count is changed to 16.

   ALTER TABLE `t_order` 
   REBUILD CLUSTERED COLUMNAR INDEX `cci_test`(`seller_id`) PARTITION BY KEY(`id`) PARTITIONS 16;

3. Verify the new table schema.

   SHOW CREATE TABLE t_order\G

   The output shows that the CCI definition has been updated successfully.

   *************************** 1. row ***************************
          Table: t_order
   Create Table: CREATE TABLE `t_order` (
   	`id` bigint(11) NOT NULL AUTO_INCREMENT,
   	`order_id` varchar(20) DEFAULT NULL,
   	`buyer_id` varchar(20) DEFAULT NULL,
   	`seller_id` varchar(20) DEFAULT NULL,
   	`order_snapshot` longtext,
   	PRIMARY KEY (`id`),
   	CLUSTERED COLUMNAR INDEX `cci_test` (`seller_id`)
   		PARTITION BY KEY(`id`)
   		PARTITIONS 16
   ) ENGINE = InnoDB DEFAULT CHARSET = utf8
   PARTITION BY KEY(`id`)
   PARTITIONS 16