SQL Reference

Table Collocation

DB2 has the capability of recognizing when the data accessed for a join or subquery is located at the same partition in the same nodegroup. When this happens DB2 can choose to perform the join or subquery processing at the partition where the data is stored, which often has significant performance advantages. This situation is called table collocation. To be considered collocated tables, the tables must:

• be in the same nodegroup (that is not being redistributed ¹⁴ )
• have partitioning keys with the same number of columns
• have the corresponding columns of the partitioning key be partition compatible (see Partition Compatibility).

OR

• be in a single partition nodegroup defined on the same partition.

Rows in collocated tables with the same partitioning key values will be located on the same partition.

¹⁴

While redistributing a nodegroup, tables in the nodegroup may be using different partitioning maps - they are not collocated.