Where tables have many-to-many relationships, you have a choice of strategies for
handling queries that need to join non-colocated data.
Note: Joins are permitted only on data that is colocated. Query execution can be distributed and
executed in parallel, but the joined rows in each partition member have to be
restricted to other rows in the same partition.
For tables have many-to-many relationships, choose one of the
following strategies to handle queries that need to join non-colocated data:
- Use parallel, data-aware
procedures to run the logic for the query on the member that stores some or all
of the data (to minimize data distribution hops). The procedure should execute
multiple queries and then join the results using application code.
Programming Data-Aware Procedures and Result Processors
provides more information.
- Split the query into
multiple queries, and perform the join in the application client code.