@Rocky when adding a colocate group, do I need to specify the database name, if that group belongs to another database ?

No, you do not need to specify the database name in the

colocate_with

property, even if the group was originally created in a different database. Since StarRocks v2.5.4, colocate joins are supported across different databases. To use this feature, you simply specify the same group name in the

colocate_with

property for all tables you wish to colocate. Key Points: • Same Name: Use the identical string for

"colocate_with"

across tables in different databases (e.g.,

"colocate_with" = "group1"

). • Internal Handling: Although StarRocks internally prefixes the group name with a database ID (e.g.,

dbId_groupName

), users only interact with the

groupName

. When you use the same name in a different database, StarRocks automatically manages the mapping to ensure they are colocated. • Verification: You can verify the groups across databases by running:

sql
  SHOW PROC '/colocation_group';

This will show the Colocation Groups existing in each database with their respective IDs. Example: If you have

table1

in

db1

and want to colocate

table2

in

db2

with it:

sql
-- In db1
CREATE TABLE table1 (...)
DISTRIBUTED BY HASH(k1) BUCKETS 8
PROPERTIES("colocate_with" = "my_shared_group");

-- In db2
CREATE TABLE table2 (...)
DISTRIBUTED BY HASH(k1) BUCKETS 8
PROPERTIES("colocate_with" = "my_shared_group");

StarRocks will ensure both tables are distributed across the same BE nodes based on the group name, allowing for local joins. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • using_starrocks/Colocate_join.md • quick_start/shared-data.md