By default, the columnar read-only analysis instance reorganizes data based on the primary key of the table and stores it in columnar format on columnar storage nodes. This approach effectively improves query efficiency for primary key-based lookups and scan efficiency in filtering scenarios. When business queries primarily rely on non-primary key fields for filtering or sorting operations, optimal performance may not be achieved by primary key-based sorting. In such cases, you can further optimize query efficiency by customizing the columnar sorting key. A typical scenario is SaaS applications where tenant ID is frequently used as a filter condition; in this case, you can set tenant ID as the sorting key.
Attention:
Customizing the columnar sorting key triggers data reorganization. During this process, sync latency increases significantly. It is recommended that you perform this operation during off-peak hours.
Use Limits
A custom sorting key must include the primary key column of the table. If the sorting key specified by the user does not include the primary key, the system automatically appends the primary key column to the end of the sorting key.
Custom sorting keys cannot be set for tables without a primary key.
A custom sorting key can contain a maximum of 8 column fields. This limit includes the primary key column that is automatically appended.
The following column types are not supported as sorting keys: FLOAT, DOUBLE, LONGTEXT, BLOB (including TINYBLOB, MEDIUMBLOB, and LONGBLOB), BOOLEAN, and JSON.
Columns with Chinese names or those containing special characters cannot be used as sorting keys. Column names can only contain letters, digits, and underscores.
For a table with a custom sorting key, performing DROP COLUMN, MODIFY COLUMN, RENAME COLUMN, CHANGE COLUMN, or DROP PRIMARY KEY on a column within the sorting key will cause data sync for that columnar table to be interrupted.
Steps
Creating a Custom Sorting Key Table via a CREATE Statement
When creating a table in a read/write instance, you can specify the sorting key in the COMMENT clause of the table definition using order_key=column1,column2. Separate multiple columns with English commas. If the COMMENT contains other information, use a space to separate order_key from the other content for better readability.
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 Sorting Keys via an ALTER Statement
For an existing table, you can execute ALTER TABLE in a read/write instance to modify the COMMENT and set or change a custom sorting key for the table.
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 Sorting Key Modification Results
Currently, an API for querying the progress of sorting key adjustments is not provided. During the modification of the sorting key, the table still supports queries. Because changing the sorting key triggers data reorganization, sync latency increases during this period. You can determine whether the sorting key change is complete by observing the sync latency of the columnar table.
Usage Recommendations
The order_key keyword must use the underscore form (order_key). Other formats such as orderkey and orderKey are not supported.
You can specify order_key only once within the same COMMENT. Do not write it multiple times.
Sorting key column names cannot be duplicated.
It is recommended that you modify the sorting key during off-peak hours to prevent increased sync latency from affecting the data timeliness of analytical queries.