ALLBASE/SQL Reference Manual (36216-90216)

74 Chapter2
Using ALLBASE/SQL
Understanding Data Access Paths
Hash structures operate like unique indexes; that is, they enforce the uniqueness of each
key in the table. If you attempt to insert a duplicate key, ALLBASE/SQL will return an
error message.
Differences between Hashed and Indexed Access
Hashing may provide faster access than B-tree lookups for many types of common queries,
and it does not require the overhead of additional file space required by B-tree indexes. In
addition, hashing is not subject to the overhead of updating index pages when you insert or
modify rows. However, updating key values in a hash table requires you to delete the row
containing the key value and then insert a row containing the new value. This means that
you should choose a non-volatile key for hashing whenever possible.
When to Use a Hash Structure
Hashing offers high performance when you need essentially random access to individual
tuples. It is not appropriate for applications that require sorting of the query result. In
cases where both random access and sorting are required at different times, you can define
a B-tree index as well as a hashing structure. This allows the optimizer the choice of the
most efficient method for the specific query.
The best candidates for the use of hash structures are applications in which the following
occur:
• Keys are not frequently updated. Remember that you cannot use the UPDATE statement
on hash key columns. This means that you must delete and then insert rows that
contain changes to key values.
• Most queries contain EQUAL factors on hash key columns.
• Tuples are of fixed size, with a minimum of VARCHARS and NULL values.
You should
not use a hash structure if your queries need to scan large areas, for instance,
with BETWEEN clauses or with predicates containing <> factors.
TID Access
Each row of a table has a unique address called the tuple identifier, or TID. TID
functionality provides the fastest possible data access to a single row. You can obtain the
TID of any row with the SELECT statement. For more information on TID access refer to
the ALLBASE/SQL application programming manual for the language you are using.