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

replies inline.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c32k6m$9e5$1_at_hercules.btinternet.com>...
> 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.

Jonathan,

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.

Paul

> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Mark" <simmons_mark_at_yahoo.com> wrote in message
> news:5366fb41.0403121446.19a6faed_at_posting.google.com...
> > 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