ASA SQL User's Guide
Query Optimization and Execution
Indexes
Types of index
A hash B-tree index does not store the actual row value(s) from the table. Instead, a hash B-tree index stores an order-preserving encoding of the original data. The number of bytes in each index entry used to store this hash value is termed the hash size, and is automatically chosen by the server based on the declared width of all of the indexed columns. The server compares these hashed values as it searches through an index to find a particular row.
Adaptive Server Anywhere must represent values in an index to decide how to order them. For example, if you index a column of names, then it must know that Amos comes before Smith.
For each value in your index, Adaptive Server Anywhere creates a corresponding hash value. It stores the hash value in the index, rather than the actual value. Adaptive Server Anywhere can perform operations with the hash value. For example, it can tell when two values are equal or which of two values is greater.
When you index a small storage type, such as an integer, the hash value that Adaptive Server Anywhere creates takes the same amount of space as the original value. For example, the hash value for an integer is 4 bytes in size, the same amount of space as required to store an integer. Because the hash value is the same size, Adaptive Server Anywhere can use hash values with a one-to-one correspondence to the actual value. Adaptive Server Anywhere can always tell whether two values are equal, or which is greater by comparing their hash values. However, it can retrieve the actual value only by reading the entry from the corresponding table.
When you index a column containing larger data types, the hash value will often be shorter than the size of the type. For example, if you index a column of string values, the hash value used is at most 9 bytes in length. Consequently, Adaptive Server Anywhere cannot always compare two strings using only the hash values. If the hash values are equal, Adaptive Server Anywhere must retrieve and compare the actual two values from the table.
For example, suppose you index the titles of books, many of which are similar. If you wish to search for a particular title, the index may identify only a set of possible rows. In this case, Adaptive Server Anywhere must retrieve each of the candidate rows and examine the full title.
An ordered sequence of columns is also called a composite index. However, each index key in these indexes is at most a 9 byte hash value. Hence, the hash value cannot necessarily identify the correct row uniquely. When two hash values are equal, Adaptive Server Anywhere must retrieve and compare the actual values.