Hash clusters aka "poor man's partitioning" or, in today's jargon "poor
non-birthing person's partitioning", stores the data with the same value
of the hash function into the same block. Hash clusters are usually
organized around the low cardinality columns and this means that it is
easy to find the blocks with the column having the particular value.
It's similar to list partitioning the table on the values of a low
cardinality column but doesn't require partitioning license. Of course,
there is no truncate partition, exchange partition or rebuild partition
either. In your case, it would act similarly to bitmap indexes.
On 8/26/21 2:59 AM, Lok P wrote:
Thank you Mladen. I am not familiar with hash clusters. You mentioned cost vs benefit analysis to be done, so curious to know what is the negative side of having the hash cluster as opposed to the regular FBI index created on this normal table to cater this issue? I was trying to see if any docs stating the use cases of hash clusters to cater performance issues but not getting much on that on google. it would be great if you can point to some use cases. Thanks.