Home » RDBMS Server » Server Administration » Oracle Single Table Hash Clusters (merged)
Oracle Single Table Hash Clusters (merged) [message #388355] Tue, 24 February 2009 07:54 Go to next message
pramodkp
Messages: 4
Registered: February 2009
Junior Member

How does Oracle determine the block address by applying hash on cluster key? Is some meta data [something like a hash table] maintained in the cluster for this purpose ?

If yes,
what is the overhead per hash key?
is there a way to pin it in the buffer cache?




Re: Oracle Single Table Hash Clusters (merged) [message #388357 is a reply to message #388355] Tue, 24 February 2009 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts
Chapter 5 Schema Objects
Sections Overview of Clusters and Overview of Hash Clusters

Regards
Michel
Re: Oracle Single Table Hash Clusters (merged) [message #388453 is a reply to message #388357] Tue, 24 February 2009 22:13 Go to previous messageGo to next message
pramodkp
Messages: 4
Registered: February 2009
Junior Member
Thanks for the pointers, Mike.

But I don't find the answers there. It states that "...resulting hash value corresponds to a data block in the cluster, which Oracle then reads/writes on behalf of the issued statement"

I am keen to know if hash-tables are maintained somewhere and the total overhead per key.

Regards,
Pramod K P
Re: Oracle Single Table Hash Clusters (merged) [message #388486 is a reply to message #388453] Wed, 25 February 2009 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no hash table, there is a cluster index which gives the rows for a cluster value.

Regards
Michel
Re: Oracle Single Table Hash Clusters (merged) [message #388508 is a reply to message #388486] Wed, 25 February 2009 01:06 Go to previous messageGo to next message
pramodkp
Messages: 4
Registered: February 2009
Junior Member
Does it mean we access the cluster index first and then the data block? If so are we not doing at least 2 logical IOs to reach a row?

Thanks,
Pramod K P
Re: Oracle Single Table Hash Clusters (merged) [message #388512 is a reply to message #388508] Wed, 25 February 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, unless you only need what is in the cluster key or your statement requires a table scan.

Regards
Michel
Re: Oracle Single Table Hash Clusters (merged) [message #388546 is a reply to message #388512] Wed, 25 February 2009 06:11 Go to previous messageGo to next message
pramodkp
Messages: 4
Registered: February 2009
Junior Member
Thanks for the reply.
But I think there would not be 2 IO s with hash clusters.

Please refer to this post: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4536286321836

Here Tom says, the index IO s do not happen with hash clusters.

Regards,
Pramod K P
Re: Oracle Single Table Hash Clusters (merged) [message #388556 is a reply to message #388546] Wed, 25 February 2009 06:52 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, my apologize I mixed up B*Tree and Hash clusters.
You are right the hashing function does determine the block.
You don't need any hash table, the hash function "IS" the logical hash table and unless you have too much collisions you only need 1 LIO.

Regards
Michel
Previous Topic: ASM - Unable too see Candidate Disks
Next Topic: adding extra hard drive to my server
Goto Forum:
  


Current Time: Thu Dec 08 16:03:32 CST 2016

Total time taken to generate the page: 0.05588 seconds