@Rocky I have two tables, both using the Primary Key model. 1. Orders Table:

order_id

is both the PK and the Distribution Key. 2. Behavioral Data Table:

order_id

is present but is not part of the PK, so I cannot use it as the Distribution Key. If I join these two tables on

order_id

, will having

order_id

as the Distribution Key in the Orders table improve performance, or is a Shuffle Join inevitable for both sides since they aren't colocated? Does the distribution of one table help at all if the other isn't partitioned/distributed by the same join key?

Yes, having

order_id

as the Distribution Key in the Orders table will significantly improve performance compared to a standard Shuffle Join. Even if the Behavioral Data table is not distributed by

order_id

, StarRocks can leverage the distribution of the Orders table to perform a Bucket Shuffle Join. How it works in your scenario: * No full shuffle: A standard Shuffle Join redistributes both tables across the network. In your case, because

order_id

is the distribution key for Orders, StarRocks only needs to shuffle the Behavioral Data table. * Targeted Shuffling: StarRocks will hash the

order_id

from the Behavioral Data and send those rows directly to the specific BE nodes that already hold the corresponding buckets for the Orders table. * Local Join: Once the behavioral data arrives at those nodes, the join is performed locally using the data already present in the Orders table's buckets. Key Points: 1. Efficiency: This is much faster than a full Shuffle Join because the network traffic is halved (only one table moves). 2. Optimizer Behavior: The StarRocks cost-based optimizer (CBO) typically detects this automatically. You can verify this by running

EXPLAIN <your_query>

and looking for

BUCKET_SHUFFLE

in the join distribution type. 3. Comparison to Colocate Join: A Colocate Join (where zero data is shuffled) is indeed impossible here because both tables must share the same distribution key and belong to the same Colocation Group. However, the Bucket Shuffle Join is the next best optimization available. 4. Join Order: To ensure the optimizer chooses this, the table distributed by the join key (Orders) should usually be on the "left" side of the join (though the CBO often reorders this for you). Summary: Your design is beneficial. The distribution of the Orders table is not wasted; it allows the system to avoid shuffling that table entirely during the join. References • best_practices/bucketing.md • using_starrocks/Colocate_join.md • table_design/data_distribution/feature-support-data-distribution.md • table_design/data_distribution/Data_distribution.md