Re: Hash clusters

In our last gripping episode blue <blue_at_yahoo.com> wrote:
> We are running Oracle 8 on Solaris 2.6
>
> A developer has placed a hash cluster on one of our tables,
> and the performance of this table has been degrading ever since.
> It is not necessarily static data -- altho the amount doesn't
> necessarily grow much -- just a lot of inserts and deletes.
> This table gets analyzed on a regular basis.
>
> I've noticed sql jobs that run against this particular table are now
> doing
> full table scans, and index hints don't work either.
>
> Could someone provide some info/views on when hash clusters are
> effective,
> how to size the keys, rebuilding guidelines, etc.? If not, maybe some
> good links that give some guidelines?
>
> TIA
>
>

A hash cluster is not placed on a table, the table is placed into a hash cluster.

Hash clusters are effective when the tables in the cluster are fairly static in size and most queries are equality queries on the cluster key. With equality queries [WHERE cluster_key = ...] the hashing algorithm is effective in locating the key value, thus reducing I/O.

Hash clusters are not effective when the tables in the cluster are not static and/or the queries are non-equality queries [WHERE cluster_key < ... , as an example].

My thoughts tend to focus on the inserts and deletes on the table in the hash cluster. Since the overall size of the table is increasing, even slightly, the cluster key values are changing [due to the inserts and deletes] and the number of rows cannot be determined to any reliable degree the hash function has trouble determining an accurate distribution of cluster key values. Performance will suffer under these conditions, of which you are well aware.

Under these conditions your table is not a good candidate for a hash cluster. However dropping the cluster will also drop the table and all associated indexes. You might try to copy the table as a non-clustered table and rebuild the non-cluster indexes on it:

create table <newtab>
as select *
from <clusteredtab>
/

(Hopefully you have the script used to initially create the table which should include the indexes; you can then re-create the table with a different name and populate it with data from the clustered table, then drop the cluster and rename the new table to the original name.)

These are my thoughts on the matter; other opinions are more than welcome.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.