Databases Reference
In-Depth Information
collocated, the shaded section of data for table A on each node will join with the shaded
section of data in table B. However, in a worst-case scenario the entire join data for one
of the tables will need to be transferred to the other's nodes for joining.
This represents a very interesting and challenging design goal of trying to find par-
titioning keys that achieve good collocation while still providing even data distribution
across the nodes.
You can be certain that tables are collocated if they have the same number of col-
umns in the partitioning key, and if the data types of the corresponding columns are
node compatible. Rows in collocated tables with the same partitioning key value are
always placed on the same database partition.
6.7
Database Design Tips for Reducing Cross-node
Data Shipping
The first set of techniques is based on careful selection of the partitioning columns. The
second set is based on various forms of duplicated partitioning of subsets of data.
6.7.1 Careful Partitioning
The following guidelines can help. Fortunately, there are some other practices that help
ensure collocation of join tables and reducing the amount of cross-node data shipping
and cross-node activity that occur during workload processing.
•
In an OLTP environment, when the partitioning columns are equivalent to
equality predicates using constants or host variables then the database can deter-
mine before query execution exactly which node holds the required data simply
by hashing the predicate's operand. For example, consider a table Employees
with an employee identifier “
emp_id”
often used in transactions such as:
UPDATE EMLPOYEES SET <... etc...>
WHERE emp_id = <host-variable>
In this case, partitioning on the emp_id column would allow the database to
determine before execution exactly which nodes need to process the UPDATE
command.
•
Join columns are a classic choice for partitioning columns, provided they don't
result in massive data skew across the nodes.
