tencent cloud

Customizing Column-Format Storage Ordering Keys
Download PDF
Last updated:2025-10-27 16:30:50
Customizing Column-Format Storage Ordering Keys
Last updated: 2025-10-27 16:30:50
Download PDF
The analysis engine by default reorganizes data based on the primary key of the table, converting it to column-storage format and saving it in the column store node. This storage method can significantly improve the efficiency of queries based on the primary key as well as the efficiency of scans in filtering scenarios. However, if business queries mostly depend on other fields for filtering or ordering, the ordering mechanism based on the primary key may not fully leverage its performance benefits. In this case, you can further optimize SQL query efficiency by adjusting the ordering key of the column storage table. A typical example is SaaS business scenarios where tenant ID is often used to filter queries. In these scenarios, tenant ID can be set as the ordering key.
Note:
Currently, only the analysis engine kernel versions 2.2410.11.1, 3.2503.4.0, and later support customizing column-format storage ordering keys.
Customizing column-format storage ordering keys can suspend synchronization. It is recommended to perform the operation during off-peak business hours.

Scenarios Where Customizing Column-Format Storage Ordering Keys Is Not Supported

The custom column-format storage ordering key should include a primary key field. The analysis engine determines whether a primary key is included. If it is not included, it implicitly adds the primary key field to the ordering key.
Columns such as float, double, longtext, blob, boolean, and json are not supported as custom ordering keys.
Customizing column-format storage ordering keys is not supported for tables without primary keys.
After customizing a column-format storage ordering key for a table, performing DDL operations (dropping, modifying, renaming, or changing columns, or dropping the primary key) on the ordering key columns can interrupt the synchronization of the column storage table. For example, if the ordering key is modified to "id, c1, c2", performing such operations on these three columns in the primary node will interrupt synchronization.
A custom column-format storage ordering key can include up to eight fields.
Columns where names contain Chinese characters or special characters are not supported as ordering keys.

Operation Steps

Creating a Custom Column-Format Storage Ordering Key Table with the CREATE Statement

To create a table in the primary instance, you can add order_key=column1,column2 information to the COMMENT section of the table definition statement, separating it from other comments with a space.
CREATE TABLE table_name (
    ...
) COMMENT 'order_key=column_name[,column_name]';

## Example:
CREATE TABLE part (
    p_partkey     BIGINT NOT NULL PRIMARY KEY,
    p_name        VARCHAR(55) NOT NULL,
    p_mfgr        CHAR(25) NOT NULL,
    p_brand       CHAR(10) NOT NULL,
    p_type        VARCHAR(25) NOT NULL,
    p_size        BIGINT NOT NULL,
    p_container   CHAR(10) NOT NULL,
    p_retailprice DECIMAL(15,2) NOT NULL,
    p_comment     VARCHAR(23) NOT NULL
) COMMENT 'Part information table order_key=p_brand,p_container,p_type';

Modifying Table Ordering Keys with the ALTER Statement

For tables with data already imported, you need to set a custom column-format storage ordering key by executing the ALTER statement in the primary instance. The syntax is as follows:
ALTER TABLE table_name COMMENT 'order_key=column_name[,column_name]';

## Example:
ALTER TABLE part COMMENT 'Part information table order_key=p_brand,p_container,p_type';

Confirming Ordering Key Modification Success

Currently, the adjustment progress of the ordering key cannot be viewed. However, during the adjustment process, you still can query table data. Although data synchronization will be paused during the ordering key adjustment process, you can monitor the synchronization delay to confirm whether the ordering key has been modified.

Non-Standard Ordering Key Syntax

An ordering key contains duplicate column names:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey,p_type,p_partkey COLUMNAR=1';
A non-existing column is specified as the ordering key:
ALTER TABLE part COMMENT 'Part information table order_key=column';
Keyword error:
ALTER TABLE part COMMENT 'Part information table ORDERKEY=column';
No space is added to separate from other COMMENT information:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey';
Multiple ordering key keywords are used consecutively:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey order_key=p_partkey';
Ordering key keywords are empty:
ALTER TABLE part COMMENT 'Part information table order_key= COLUMNAR=1';
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback