Re: Hash clusters

In our last gripping episode blue <blue_at_yahoo.com> wrote:
> Looks like there was only one index on the table (the Primary key).
> This table is responsible for quite a bit -- massive amounts of
 inserts,
> deletes, updates via a java program, and a job that runs once a day
> selecting into another table.
>
> I need to go thru and determine other potential indexing -- but don't
 want
> to overdo it, as too many indexes will kill performance too.
>
> Besides a hash cluster, I must ask what other approach to take.
> (I haven't been here very long, and it sounds like it's been a
 problem for
> awhile).
>
> 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.
>
>

I cannot recommend a hash cluster for a table with large numbers of inserts/deletes/updates.

I would, quite simply, index the table for the most often executed queries and I would keep the number of indexes to no more than five (more indexes can cause heartburn for the optimizer and can slow insert/update performance due to the large number of indexes to maintain). These can, of course, be concatenated (or multi-column) indexes. Presuming there is an application to manipulate the data index for the most often used queries/transactions from the application if possible.

Remember, too, that for an index to be effective the leading column in that index must be utilized as selection criteria, i.e., in the WHERE clause of the query. This may help in your creation of indexes.

Ad hoc queries against this table will likely be slow once the table is fully indexed as many ad hoc queries will not utilize these indexes. These types of queries should be few and far between especially if there is an application that accesses/modifies the data.

I hope this helps. Feel free to contact me via email.

--
David Fitzjarrell
Oracle Certified DBA


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