Re: Hash clusters

I agree. I also mispoke, as I know tables are created within clusters. Thanks

David Fitzjarrell wrote:

> 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.
Received on Tue Nov 14 2000 - 17:12:20 CST