The efficiency of a join operation depends on whether the WHERE condition uses values for columns on which primary, secondary, or multitable join indexes are defined.
If indexes are defined on the dept_no columns in both the employee and department tables, specifying an equality condition between the values in each indexed column, as in the preceding example, allows the rows in the two tables to be matched using the values in both indexed columns.
Efficiency is increased if a primary index is defined on one or both of these columns. For example, define dept_no as the unique primary index for the department table. This is not possible if one or both of the tables being joined is an ordinary NoPI table or a NoPI column-partitioned table. See NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions for suggestions about how to work around this potential problem.
For all-AMP tactical queries against row-partitioned tables, you should specify a constraint on the partitioning column set in the WHERE clause.
If a query joins row-partitioned tables that are partitioned identically, using their common partitioning column set as a constraint enhances join performance still more if you also include an equality constraint between the partitioning columns of the joined tables.