Hi!
I was reading up on declarative partitioning[1] and I'm not sure what could be a possible application of Hash partitioning.
Is anyone actually using it? What are typical use cases? What benefits does such a partitioning scheme provide?
On its face, it seems that it can only give you a number of tables which are smaller than the un-partitioned one, but I fail to see how it would provide any of the potential advantages listed in the documentation.
With a reasonable hash function, the distribution of rows across partitions should be more or less equal, so I wouldn't expect any of the following to hold true:
- "...most of the heavily accessed rows of the table are in a single partition or a small number of partitions."
- "Bulk loads and deletes can be accomplished by adding or removing partitions...",
etc.
That *might* turn out to be the case with a small number of distinct values in the partitioning column(s), but then why rely on hash assignment instead of using PARTITION BY LIST in the first place?