Distribution Guidelines
The key to solving such a dilemma is to prototype several possibilities to see which is the best choice. You might have to make do with an optimal, rather than a maximal, solution.
The goal of this guideline is to optimize row distribution without worsening row accessibility.
With respect to partitioned primary indexes, the granularity of partitioning can be a significant factor in the general effectiveness of the index. The more row partitions, the finer the ability to eliminate them. At the same time, too fine a granularity can have a negative effect on primary index access as well as joins and aggregations on the primary index. See “Row‑Partitioned and Nonpartitioned Primary Index Access for Typical Operations” on page 351 for a detailed comparison of the various access mechanisms for the two primary index types over a range of typical operations and conditions.