ALLBASE/SQL Reference Manual (36216-90216)

Chapter 2 73
Using ALLBASE/SQL
Understanding Data Access Paths
uniqueness; duplicate values cannot exist in the hash key column(s). A well-chosen hash
key, like a good index key, provides the optimizer with the choice of a potentially faster
data access method than a serial scan.
Create a hash structure at the time you create a table. In addition to the components of a
table definition, a hash structure definition includes:
1. Columns that define the hash key
2. Number of primary pages
The reference numbers in the following example refer to the table definition components
listed above:
CREATE PUBLIC TABLE PurchDB.Vendors
(VendorNumber INTEGER NOT NULL,
VendorName CHAR(30) NOT NULL,
ContactName CHAR(30),
PhoneNumber CHAR(15),
VendorStreet CHAR(30) NOT NULL,
VendorCity CHAR(20) NOT NULL,
VendorState CHAR(2) NOT NULL,
VendorZipCode CHAR(10) NOT NULL,
VendorRemarks VARCHAR(60) )
UNIQUE HASH ON (VendorNumber) -- 1
PAGES = 101 -- 2
IN PurchFS
Use the UNIQUE HASH clause or the HASH ON CONSTRAINT clause to specify one or more
columns for a hash key. Use the PAGES= clause to define a number of primary pages in
which to store the data in the table. This is different from ordinary data storage, which
does not require a number of primary pages.
Based on the key and the number of primary pages you specify, ALLBASE/SQL calculates
a page number for each row before insertion into the table. The page number depends
directly on the data in the key. Because a specific number of primary pages is specified, you
must create the hash structure as you create the table; you cannot modify a table from
normal to hash storage at a later time.
The optimizer
can decide to use hashed access provided the statement contains a WHERE
clause with an EQUAL factor for each column in the hash key. This makes hashing
especially useful for tables on which you need quick random access to a specific row.
For example, assuming you have defined a hash key on VendorNumber, the optimizer
might choose hashed access for the following:
isql=> SELECT * FROM PurchDB.Vendors
> WHERE VendorNumber = 9002;
However, it would not consider hash access for the following:
isql=> SELECT * FROM PurchDB.Vendors
> WHERE VendorNumber > 9002
> ORDER BY VendorName;