Hash cluster vs Index Cluster

[whatsinaname99]

What is the difference between a hash cluster and an index/b-tree cluster such that the hash cluster is to be preferred when running "equality" queries and the index/b-tree is to be preferred when searching a range of values?

 

[Thargy]

Could you please clarify why you need to know this?

I note from your profile that your first post was a newbie post (by your own definition). I'm therefore somewhat surprised that you are asking what I believe to be a very advanced question, so soon, on what is only your second post.

It reads like an exam question, or some other "prepared" question, rather than a genuine need for a solution to a problem.

If you really do have a problem, then please tell us what it is, and we'll gladly help. However, may I gently remind you that we generally don't do research and homework.

Anyway, since you ask, a hash cluster should be faster in getting rows for a particular value (assuming no collisions in your hash space, which is generally due to poor design when first establishing the cluster) the input value hashes to one and only one hash value, and precisely the right rows are fetched. There is no index, the data is the index (to quote Tom Kyte). An index cluster has (as its name implies) data from the clustered tables indexed, there is a physical index present. Therefore, for a range of values, the index will elegantly show Oracle which rows you need.

From the above, hashing will naturally be quicker when fetching just one value (the equality situation) and indexing will be faster for many values. This obviously assumes sound design of the cluster in the first place (i.e. the correct hash space, clustered and indexed columns etc.).

Regards

T

Grinding away at things Oracular

 

[whatsinaname99]

Thank you very much for your response. You explained this in a way I could understand.

Thank you for your gentle reminder.
This question was not related to an immediate problem I was having. I'm sorry, but I didn't realize that the site was directed more towards real-world problems.
My question was also not from an exam. I was reading Tom Kyte's book, as it happens, and trying to understand the differences between the various Oracle tables and under what circumstances one type would be favored over another. I did a number of searches online last night and didn't turn up a "plain English" explanation. I think if I had given it another day I would have arrived at a comprehensible answer or have been able to make more sense of what I did find, but I got kind of impatient.

 

[Thargy]

Ah, commendable application for a newbie.

I think Mr Kyte's books are superb, and in them you have found a world-class reference source. Since you obviously take this subject seriously (otherwise why would you be doing such reading?) may I suggest that you try to obtain joined-up knowledge.

If you really are a newbie, get to grips with the fundamentals first, and move on to the advanced stuff later. Knowledge in isolation can be dangerous, as you might do what appears best, only to realise later that it was counter productive.

Get your schemas, tables, constraints and permissions down solid, along with sqlplus scripting, and at least a smidgeon of PL/SQL. Only when your basic tool kit with screwdrivers, hammers and spanners is in place, should you move on to the fancy cordless power tools.

Regards

T

Grinding away at things Oracular

 

[whatsinaname99]

Thanks for your feedback.
Though I'm new, I'm not that new.
I'm not doing this in a hapazard way, though, but studying things by topic. Right now, the topic of the day is tables; I want to understand them in-depth. Of course, the level of depth is subjective. I doubt I'll go as low, say, as the applied mathematics that comes into play, but deep enough so that I'm satisfied.

 

[Thargy]

Fair enough,

sorry if that came across as patronising, I just wanted to be sure that you weren't maintaining law and order in the kindergarten with a shotgun!

I rarely have enough time to do the in-depth study that Oracle sometimes needs, so if you do unearth any gems, would you please be good enough to post them here.

We aim to help one another, so all well researched thoughtful input is most welcome.

Regards

T

Grinding away at things Oracular