PolarDB:Rebuild a clustered columnar index

Prerequisites

Before you begin, make sure that:

• A CCI already exists on the target table

• Your instance runs version 5.4.20-20250714 or later

For version naming rules, see Release notes. To check your instance version, see View and update the version of an instance.

When to rebuild a CCI

Rebuild a CCI when any of the following applies:

• The current sort key or partition key no longer matches your query access patterns, and query performance is below expectations

• Columns used in the index have been added, removed, or modified, and the associated queries have changed

• Data volume has grown to a point where the original partitioning strategy creates uneven distribution or too many or too few partitions

• A time-based column would better distribute data across partitions for large, time-series datasets

• A database upgrade introduces new CCI capabilities that require rebuilding to activate

How it works

The rebuild creates a new CCI with the updated definition, then drops the original CCI. Because both CCIs temporarily coexist during this process, you must raise the MAX_CCI_COUNT limit before starting.

The rebuild runs online and does not acquire locks, so existing queries on the CCI and the underlying row-based table continue running without interruption.

Rebuild a CCI

1. Set the MAX_CCI_COUNT variable

   Set MAX_CCI_COUNT to at least 2 to allow the old and new CCI to coexist temporarily.

   SET GLOBAL MAX_CCI_COUNT = 2;

   Note

   If the table already has multiple CCIs, set MAX_CCI_COUNT to a value higher than the current total number of CCIs on that table.

2. Rebuild the index

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

   Mode	Behavior	When to use
   Synchronous	Blocks the current session until the rebuild completes	Small to medium tables where rebuild time is short
   Asynchronous (recommended)	Returns immediately; rebuild runs in the background	Large tables where a long rebuild would block the session

   Synchronous rebuild

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

   For the full CCI definition syntax, see Create and use a CCI.

   Note

   • The specified tbl_name and index_name must exist; otherwise, an error is returned.

   • Archived CCIs cannot be rebuilt.

   Asynchronous rebuild (Recommended for large tables)

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

Example

This example changes the sort key from id to seller_id and increases the partition count from 4 to 16.

1. Create a table with an initial CCI.

The sort key and partition key are both set to id, with 4 CCI partitions.

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. Increase the CCI limit.

SET GLOBAL MAX_CCI_COUNT = 2;

3. Rebuild the CCI with the new definition.

Sort key changes to seller_id; partition count changes to 16.

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

4. Verify the updated schema.

SHOW CREATE TABLE t_order\G

The output confirms that the CCI definition has been updated:

*************************** 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

What's next

• Create and use a CCI — CCI definition syntax and options

• Release notes — version history and new CCI capabilities