Re: Fixing Performance issue with less selective columns

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.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l