Re: Hash Cluster Bennies

mdg_at_netcom20.netcom.com (Mark Grand) wrote:

>In article <1995Jul31.171314.4965_at_den.mmc.com> Wasteland Warrior <jbuhl_at_den.mmc.com> writes:
 

> What are the negative impacts or pitfalls of using hash clusters
> in Ov7.
 

>Here are the bad points I know about:
 

>* Accessing a single table in a cluster (hash or indexed) is slower
> than accessing an identical unclustered table.
 

>* The hash cluster does not help to access rows in any particular
> sequence. Indexed cluster do not have this shortcoming.
 

>* When you create a hash cluster, you need to decide on a maximum
> number of cluster values. If you eventually need to exceed that
> maximum, you will need to rebuild the cluster an dall of the tables
> in it.
>--
>==============================
>Mark Grand

I've not used hashed clusters in Oracle but the two benefits I can think of right off the top of my head would be reduced disk requirements, and faster access to individual rows. Less space because you don't need to store a cluster index, and faster individual row access because you don't have to locate the key in an index 1st, then read the cluster 2nd. The hashing algorithm takes right to the block that contains the row(s) in question.

Do you really need to rebuild a hashed cluster if you exceed the max # of values? This doesn't sound right. How do you exceed the max # of values? The number of hashkeys is just a number used in the hashing algorithm. Usually there's some computation done on the cluster key resulting in an integer. That integer is then divided by the # of hashkeys and the remainder is that hashkey that the row hashes to. You want to make sure that there's enough hashkeys to spread out the rows in the cluster so that not too many hash to each hashkey. But the nature of hashing is that this *will* happen on some groups, especially if the cluster key is not a sequential #. There has to be some means of handling this or else hash clusters are totally unreliable and useless.

Like I said, I've not used hashed clusters (or clusters period fo that matter) in Oracle and I'm basing this on what I know about hashing from other databases (Pick & Universe). Do some benchmark tests first but I think you'll find what I'm saying to be true.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

If at first you don't succeed, sky-diving isn't for you.