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

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.

-- 
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

>

> drak0nian_at_yahoo.com (Paul Drake) wrote in message

news:<1ac7c7b3.0403120939.39511a5a_at_posting.google.com>...


> > If you attended Steve Adams presentation yesterday at the Hotsos

> > Symposium, you probably have quite a list of new methods (not tips and

> > tricks) that you are looking forward to test and possibly introduce

> > into your environment.

> >

> > I wanted to open this thread for anyone that cared to share their

> > results from moving from heap tables + b+tree indexes to either IOTs,

> > index clusters, hash clusters or single table hash clusters.

> >

> > still seeing block dumps when I close my eyes ...

> >

> > Pd