Workload Characteristics and Joins
Consider the following points when you write join queries against a partitioned table:
Consider including the partitioning column in the nonpartitioned primary index table so you can join on the partition column. This means that, depending on the situation, you might want to consider denormalizing the physical schema to enhance the performance of partitioned table‑to‑nonpartitioned table joins.
Otherwise, the table might 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 SQL Request and Transaction Processing).
To ensure that the Optimizer creates good query plans for your partitioned tables, you should always collect PARTITION statistics and keep them current.
If you have not collected PARTITION statistics, the Optimizer does not know whether a combined row partition is empty or not, so it has to assume all defined combined row partitions might have rows with respect to the plan it generates; however, it might choose among several such plans based on the estimated number of populated combined row partitions.
Remember to collect statistics (see “COLLECT STATISTICS (Optimizer Form)” in SQL Data Definition Language) on all of the following.
The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS statement. See “COLLECT STATISTICS in SQL Data Definition Language for details on how to do this.