@Rocky Can we use an alter table to change the sort order and the number of buckets of a table as a single asynchronous operation?

You can change the sort order and the number of buckets using

ALTER TABLE

, but you cannot combine them into a single asynchronous operation. You must perform them sequentially as separate commands. Key Rules for These Operations 1. Asynchronous Nature: Both changing the bucketing method (

DISTRIBUTED BY

) and the sort order (

ORDER BY

) are asynchronous operations. They require a background data rewrite. You can monitor their progress using

SHOW ALTER TABLE COLUMN

. 2. Concurrency Limit: StarRocks allows only one ongoing schema change operation per table at a time. If you attempt to change the sort order while a bucket modification is still in progress, the command will be rejected. 3. Syntax Restrictions: While

ALTER TABLE

supports multiple clauses (like adding multiple columns at once), specific operations like modifying the distribution/bucketing cannot be combined with other schema changes in a single statement. Sequence for Execution To achieve both changes, you should follow this workflow: 1. Modify the sort order (Supported for all table models as of v3.3):

sql
    ALTER TABLE my_table ORDER BY (new_sort_col1, new_sort_col2);

2. Check status: Wait until the

State

of the job is

FINISHED

.

sql
    SHOW ALTER TABLE COLUMN FROM my_database;

3. Modify the number of buckets (Supported as of v3.2): Note that even if you only want to change the bucket count, you must re-specify the bucketing column.

sql
    ALTER TABLE my_table DISTRIBUTED BY HASH(my_dist_col) BUCKETS 20;

Important Version Notes * Bucket Modification: Requires StarRocks v3.2 or later. (In shared-data/Cloud-native mode, this is supported from v4.0). * Sort Order Modification: Supported for Primary Key tables since v3.0, and for Duplicate, Aggregate, and Unique Key tables since v3.3. * Shared-data (Cloud-native): Some early v3.x versions of shared-data mode did not support modifying the distribution; ensure you are on a version that supports "Data Distribution Optimization" if using this mode is cloud-native. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md

Is it better to first change the order and then the buckets? @Rocky

There is no strict requirement from the database, but it is generally better to change the Sort Order (

ORDER BY

) first, followed by the Number of Buckets (

DISTRIBUTED BY

). Why Sort Order First? When you change the number of buckets, StarRocks performs a background data redistribution. This process involves reading data from the old tablets and writing it into new tablets. 1. Efficiency of the Rewrite: During a bucket modification, StarRocks writes the data to the new tablets according to the current sort order of the table. * If you change the sort order first, the subsequent bucket change will write the data into the new tablets using your new desired sort order. This essentially accomplishes the sorting "for free" (or at least as part of the redistribution) in the second step. * If you change the buckets first, the data will be redistributed using the old sort order. You will then have to trigger a second full data rewrite just to change the sorting. 2. Resource Management: Both operations are "heavy" (IO and CPU intensive). By setting the sort order first, you ensure that the final state of the data in the new buckets is exactly what you want, potentially avoiding extra background compaction or re-sorting work later. Recommended Workflow 1. Step 1: Change the Sort Order

sql
    ALTER TABLE my_table ORDER BY (new_col1, new_col2);

2. Step 2: Monitor Completion Use

SHOW ALTER TABLE COLUMN;

and wait until the state is

FINISHED

. 3. Step 3: Change the Number of Buckets

sql
    ALTER TABLE my_table DISTRIBUTED BY HASH(join_key) BUCKETS 20;

4. Step 4: Final Check Monitor progress again with

SHOW ALTER TABLE COLUMN;

. Summary Comparison Sequence | Performance Impact | Why? Order $\rightarrow$ Buckets | Better | The bucket rewrite will organize data in the new order during redistribution. Buckets $\rightarrow$ Order | Suboptimal | You rewrite the table to redistribute it, then immediately rewrite it again to sort it. Note on Versioning: Ensure you are on StarRocks v3.3+ for changing the sort order on Duplicate/Aggregate/Unique tables, and v3.2+ for changing bucket counts. References • table_design/data_distribution/Data_distribution.md • best_practices/table_clustering.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/bucketing.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md