Colocating Related Rows from Multiple Tables

When two tables are partitioned on columns and colocated, it forces partitions having the same values for those columns in both tables to be located on the same GemFire XD member. For example, with range or list partitioning, any rows that satisfy the range or list can be colocated on the same GemFire XD member for all colocated tables. Colocating the data of two tables based on a partitioning column's value is a best practice if you will frequently perform queries on those tables that join on that column.

The COLOCATE WITH clause specifies the tables with which the partitioned table must be colocated. The tables that are referenced in the COLOCATE WITH clause must exist at the time you create the partitioned table.

When you specify the COLOCATE WITH clause, you must use a PARTITION BY clause to specify the same partitioning values and partitioning strategy that are used in the target table (the table referenced in the COLOCATE WITH clause). For example, if you use PARTITION BY PRIMARY KEY or PARTITION BY COLUMN in the target table, then you should use one of the same clauses to partition the colocated table on the same column values (if you partitioned using multiple columns, specify the columns in the same order).

Note: All rows that need to be colocated across multiple tables must carry the partition key. While the column names in each table may be different, the values must be the same.
For example, if you create the partitioned table, "countries," as follows:
CREATE TABLE COUNTRIES 
( 
  COUNTRY VARCHAR(26) NOT NULL CONSTRAINT COUNTRIES_UNQ_NM Unique, 
  COUNTRY_ISO_CODE CHAR(2) NOT NULL CONSTRAINT COUNTRIES_PK PRIMARY KEY, 
  REGION VARCHAR(26), 
  CONSTRAINT COUNTRIES_UC
    CHECK (country_ISO_code = upper(country_ISO_code) ) 
) PARTITION BY PRIMARY KEY;
You can colocate another table, "cities," on the same partition key using the command:
CREATE TABLE CITIES 
( 
  CITY_ID INTEGER NOT NULL CONSTRAINT CITIES_PK Primary key,
  CITY_NAME VARCHAR(24) NOT NULL, 
  COUNTRY VARCHAR(26) NOT NULL, 
  AIRPORT VARCHAR(3), 
  LANGUAGE VARCHAR(16), 
  C_COUNTRY_ISO_CODE CHAR(2) CONSTRAINT COUNTRIES_FK 
  REFERENCES COUNTRIES (COUNTRY_ISO_CODE) 
) PARTITION BY COLUMN (C_COUNTRY_ISO_CODE) 
  COLOCATE WITH (COUNTRIES);

In this example, both "countries" and "cities" are partitioned using the country ISO code keys. Rows with the same ISO code value are colocated on the same GemFire XD members. Note that while the partitioning column name in "cities" is different from the partitioning column name in "countries," both columns reference the same partitioning keys. GemFire XD ensures that the shared partitioning keys have the same type (not considering constraints), but you must ensure that the columns store the same keys.

Similarly, if you use PARTITION BY RANGE or PARTITION BY LIST in the target table to define values, you must use the same partitioning clause in the colocated table definition to specify the same partitioning keys. When PARTITION BY EXPRESSION is used with colocated tables, you must ensure that the expressions resolve to the same values in both tables, so that data can be colocated.

In all cases, the REDUNDANCY and BUCKETS, clauses must be identical for colocated tables. Colocated tables must either specify the same server groups (identical SERVER GROUPS clauses), or one table's server groups must be a subset of the other table's server groups.

See the CREATE TABLE for more information.