Single Table Hash Selects Investigated May 30, 2009
Posted by mwidlake in performance.Tags: Blogging, performance
trackback
I think I mentioned in an earlier post that Piet de Visser had posted a nice example of the fastest way to select a single row, via a single table hash cluster. Well, I somehow missed his followup until now (I think because he has two blogs and I was checking the other one).
This is the follow up and he once again provides scripts to repeat his tests. What a nice chap. He shows how care in setting up the hash cluster is a good idea.
Here’s looking forward to his next long train trip so he can add to this thread. I might even spot it sooner next time!
Martin Widlake's Yet Another Oracle Blog 
Thanks Martin.
I was hoping someone would read my blog, eventually.
A gentle correction: I have not written about joins with STCH yet. But you just raised another interesting question in my mind: Would joining an STHC on the cluster-key become a special case of join? (I’ll have to put that question to a bigger mind then me, I have more simple stuff to look into first).
The STHC topic is a bit scarey, because the more I learn, the more I find I dont know. For example you have to get your sizing Exactly right, or you risk loosing most of the advantages. And to dig deeper I find I almost _have_ to start using ten-forty-six traces and block-dumps (I’ve been using snapper by Tanel Poder to avoid reading tracefiles) Takeing a “vow of simplicity” can really complicate your life.
And my next long journey is a short overnigh flight. Not the best moment to tweak sql-scripts. Patience. I’m not about to give up yet.
Ooops, my bad. It’s the word hash, when my hands type “hash”, they just put “join” as the next word. My apologies Piet and I have updated the post title.
I know what you mean about finding out you don’t really understand something when you start to look into it; my next post is going to be on a very similar theme.
A word of caution – if you need to be looking at traces and block dumps, are you reaching the point where more and more effort is returning less and less benefit? Maybe the knowledge of how to use hash clusters for known volumes of data to reduce consistent gets and how proper sizing is important is enough?
Mind you , I like you would be digging deeper to find out exactly how it works, so I don’t take my own advice.