Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: single table hash clusters in 9.2 - anyone tried them out yet?

Re: single table hash clusters in 9.2 - anyone tried them out yet?

From: Paul Drake <>
Date: 14 Mar 2004 18:41:57 -0800
Message-ID: <>

replies inline.

"Jonathan Lewis" <> wrote in message news:<c32k6m$9e5$>...
> I wasn't at the Steve Adams' day, but at the
> Miracle Masterclass 2004, he introduced the
> very special optimisation available in single
> table hash clusters. Oracle can turn a hashkey
> into a rowid, and includes a special latching
> optimisation if you have built your hash cluster
> perfectly.
> Paul,
> If you want a prime example of how effective
> this can be, read the full disclosure document
> for Oracle's 1M transaction per minute TPC-C
> benchmark. There are about 5 PK indexes,
> and everything else is done by single table hash
> clusters.
> Mark,
> One reason why you may have had variable
> results with single table hash clusters, is that if
> you get a collision in a block, the block is flagged
> as an exception, and Oracle falls back to a more
> CPU intensive operation, with extra latching, to
> acquire the target row.


thanks much for the link (tpc).
This is from memory, but:

Steve suggested that Oracle's hash algorithm was not sufficient in uniqueness, and that a primary key (such as a sequence) would provide a better source for the hash.

In one of the block dumps, he showed a case of where the hash was not unique, which unsets a flag which represents that all rows in the block are unique. If that flag is set, oracle knows that it has a unique mapping between the hash and the row.

This is on a block by block basis, so a single non-unique mapping of hash value to row is not catastrophic.

thanks again for the excellent presentations.


> --
> Regards
> Jonathan Lewis
> The Co-operative Oracle Users' FAQ
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
> "Mark" <> wrote in message
> > I wish I could have gone.
> >
> > I have used almost every object that you had mentioned at some point
> > in time. I personally have found that you have to test to find out
> > what works best. I'm not sure why, but single table hash clusters are
> > sometimes faster than index lookups and sometimes they are slower.
> > Maybe they covered that at the symposium. :)
> >
> > Mark Simmons
> > Sr. Oracle DBA
> > Sabre-Holdings, Southlake, TX
> >
Received on Sun Mar 14 2004 - 20:41:57 CST

Original text of this message