Database Reference
In-Depth Information
The points of interest here are as follows:
•
The hash cluster did significantly less I/O (query column). This is what we anticipated. The
query simply took the random
OBJECT_ID
s, performed the hash on them, and went to the
block. The hash cluster has to do at least one I/O to get the data. The conventional table with
an index had to perform index scans followed by a table access by rowid to get the same
answer. The indexed table has to do at least three I/Os in this case to get the data.
•
The hash cluster query took the same amount of CPU for all intents and purposes, even
though it went to the buffer cache one-third as many times. This, too, could be anticipated.
The act of performing a hash is very CPU-intensive. The act of performing an index lookup
is I/O-intensive. It was a tradeoff. However, as we scale up users, we would expect the hash
cluster query to scale better, as it has to get in line to access the buffer cache less frequently.
This last point is the important one. When working with computers, it is all about resources and their utilization.
If we are I/O bound and perform queries that do lots of keyed reads like I just did, a hash cluster may improve
performance. If we are already CPU bound, a hash cluster may possibly decrease performance since it needs more
CPU horsepower to hash. However, if the extra CPU we are burning is due to spinning on cache buffers chains latches,
the hash cluster could significantly reduce the CPU needed. This is one of the major reasons why rules of thumb do
not work on real-world systems: what works for you might not work for others in similar but different conditions.
There is a special case of a hash cluster called a
single table hash cluster
. This is an optimized version of the general
hash cluster we've already looked at. It supports only one table in the cluster at a time (you have to
DROP
the existing
table in a single table hash cluster before you can create another). Additionally, if there is a one-to-one mapping
between hash keys and data rows, the access to the rows is somewhat faster as well. These hash clusters are designed
for those occasions when you want to access a table by primary key and do not care to cluster other tables with it. If
you need fast access to an employee record by
EMPNO
, a single table hash cluster might be called for. I did the preceding
test on a single table hash cluster as well and found the performance to be even better than just a hash cluster. You
could even go a step further with this example and take advantage of the fact that Oracle will allow you to write your
own specialized hash function (instead of using the default one provided by Oracle). You are limited to using only the
columns available in the table, and you may use only the Oracle built-in functions (e.g., no PL/SQL code) when writing
these hash functions. By taking advantage of the fact that
OBJECT_ID
is a number between 1 and 75,000 in the preceding
example, I made my hash function simply be the
OBJECT_ID
column itself. In this fashion, I am guaranteed to never
have a hash collision. Putting it all together, I'll create a single table hash cluster with my own hash function via:
EODA@ORA12CR1> create cluster hash_cluster
2 ( hash_key number(10) )
3 hashkeys 75000
4 size 150
5 single table
6 hash is HASH_KEY
7 /
Cluster created.
I've simply added the key words
SINGLE TABLE
to make it a single table hash cluster. My
HASH IS
clause uses the
HASH_KEY
cluster key in this case. This is a SQL function, so I could have used
trunc(mod(hash_key/324+278,555)/
abs(hash_key+1))
if I wanted (not that this is a good hash function—it just demonstrates that we can use a complex
function there if we wish). I used a
NUMBER(10)
instead of just a number. Since the hash value must be an integer, it
cannot have any fractional components. Then, I create the table in that cluster to build the hashed table:
EODA@ORA12CR1> create table t_hashed
2 cluster hash_cluster(object_id)
3 as
4 select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
5 cast( OBJECT_ID as number(10) ) object_id,
