- Specify equijoins on the primary index and partitioning column sets, if possible, to prejudice the Optimizer to use efficient RowKey-based joins (see Rowkey-Based Merge Join ).
Consider including the partitioning column in the nonpartitioned primary index table so you can join on the partition column. Therefore, depending on the situation, you may want to consider denormalizing the physical schema to enhance the performance of partitioned table-to-nonpartitioned table joins.
- If you specify an equijoin on the primary index column set, but not on the partitioning column set, the fewer combined partitions that exist after any row partition elimination, the better.
Otherwise, the table may need to be spooled and sorted.
The Optimizer can specify sliding-window joins when there are a small number of participating combined row partitions (see Sliding-Window Merge Join ).
- Use RANGE_N to define fewer partitions and specify conditions on the row partitioning columns to reduce the number of combined row partitions involved in the join by evoking partition elimination.
To make sure that the Optimizer creates good query plans for your partitioned tables, keep PARTITION statistics current.
If you have not collected PARTITION statistics, the Optimizer does not know whether a combined row partition is empty, and therefore assumes all defined combined row partitions may have rows with respect to the generated plan. However, the Optimizer may choose among multiple such plans based on the estimated number of populated combined row partitions.
- Dynamic row partition elimination for a product join improves performance when a partitioned table and another table are equijoined on the row partitioning column of the partitioned table (see Dynamic Row Partition Elimination ).Collect statistics (see COLLECT STATISTICS (Optimizer Form)) on all of the following:
- The primary indexes of both tables.
- The partitioning columns of the partitioned table.
- The column in the nonpartitioned table that is equated to the partitioning column of the partitioned table.
- The system-derived PARTITION column of all partitioned tables.
The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS statement. For details, see COLLECT STATISTICS Syntax Elements (Optimizer Form).