Database Tables - Expert Oracle Database Architecture - page 393

Select * from emp where empno = 4321
Hash{4321} = block 534
Database blocks 1..1000
Overflow blocks
Figure 10-9. Depiction of a hash cluster
When you create a hash cluster, you'll use the same CREATE CLUSTER statement you used to create the index
cluster with different options. You'll just be adding a HASHKEYS option to it to specify the size of the hash table. Oracle
will take your HASHKEYS value and round it up to the nearest prime number; the number of hash keys will always be a
prime. Oracle will then compute a value based on the SIZE parameter multiplied by the modified HASHKEYS value. It will
allocate at least that much space in bytes for the cluster. This is a big difference from the preceding index cluster, which
dynamically allocates space as it needs it. A hash cluster preallocates enough space to hold ( HASHKEYS / trunc(blocksize/
SIZE) ) bytes of data. For example, if you set your SIZE to 1,500 bytes and you have a 4KB block size, Oracle will expect to
store two keys per block. If you plan on having 1,000 HASHKEY s, Oracle will allocate 500 blocks.
It is interesting to note that unlike a conventional hash table in a computer language, it is OK to have hash
collisions—in fact, it is desirable in many cases. If you take the same DEPT / EMP example from earlier, you could set
up a hash cluster based on the DEPTNO column. Obviously, many rows will hash to the same value, and you expect
them to (they have the same DEPTNO ). This is what the cluster is about in some respects: clustering like data together.
This is why Oracle asks you to specify the HASHKEY s (how many department numbers you anticipate over time) and
SIZE (what the size of the data is that will be associated with each department number). It allocates a hash table to
hold HASHKEY number of departments of SIZE bytes each. What you do want to avoid is unintended hash collisions.
It is obvious that if you set the size of the hash table to 1,000 (really 1,009, since the hash table size is always a prime
number and Oracle rounds up for you), and you put 1,010 departments in the table, there will be at least one collision
(two different departments hashing to the same value). Unintended hash collisions are to be avoided, as they add
overhead and increase the probability of block chaining occurring.
To see what sort of space hash clusters take, we'll use a small utility stored procedure SHOW_SPACE (for details
on this procedure, see the “Setting Up Your Environment” section at the beginning of the topic) that we'll use in this
chapter and in the next chapter. This routine just uses the DBMS_SPACE -supplied package to get details about the
storage used by segments in the database.
Now, if we issue a CREATE CLUSTER statement, such as the following, we can see the storage it allocated:
EODA@ORA12CR1> create cluster hash_cluster
2 ( hash_key number )
3 hashkeys 1000
4 size 8192
5 tablespace mssm
6 /
Cluster created.