User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 25. Query Optimization       

Indexes

There are many situations in which creating an index will improve the performance of a database. An index provides an ordering of the rows of a table on the basis of the values in some or all of the columns. An index allows rows to be found quickly. It permits greater concurrency by limiting the number of database pages accessed. An index also affords Anywhere a convenient means of enforcing a uniqueness constraint on the rows in a table.

  Hash values

Adaptive Server Anywhere must represent values in an index in order 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, Anywhere creates a corresponding hash value. It stores the hash value in the index, rather than the actual value. Anywhere can perform operations with the hash value, such as 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 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, Anywhere can use hash values that have a one-to-one correspondence to the actual value. 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 that contains 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 can not always compare two strings using only the hash values. If the hash values are equal, 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, Anywhere must retrieve each of the candidate rows and examine the full title.

Composite indexes 

A composite index is one that is composed of an ordered sequence of columns. However, each index key in these indexes is at most a 9 byte hash value. Hence, the hash value can not necessarily identify the correct row uniquely. When two hash values are equal, Anywhere must retrieve and compare the actual values.

  The effect of column order in a composite index

When you create a composite index, the order of the columns affects the suitability of the index to different tasks.

Example 

Suppose you create a composite index on two columns. One column contains employee's first names, the other their last names. You could create an index that contains their first name, then their last name. Alternatively, you could index the last name, then the first name. Although these two indices organize the information in both columns, they have different functions.

CREATE INDEX fname_lname
   ON employee emp_fname, emp_lname;
CREATE INDEX lname_fname
   ON employee emp_lname, emp_lname;

Suppose you then want search for the first name John. The only index that is of useful is the one that contains the first name in the first column of the index. The index that is organized by last name then first name is of no use because someone with the first name John could appear anywhere in the index.

If you think it likely that you will need to look up people by first name only or second name only, then you should consider creating both of these indices.

Alternatively, you could make two indices that each index only one of the columns. Remember, however, that Anywhere only uses one index to access any one table while processing a single query. Even if you know both names, it is likely Anywhere will need to read extra rows, looking for those with the correct second name.

When you create an index using the CREATE INDEX command, as in the example above, the order of the columns is that shown in your command.

Primary indexes and column order 

Adaptive Server Anywhere uses a primary index to index primary keys. The primary index is a combined index: it also contains the entries for all foreign keys that reference this table, whether those foreign keys are located in the same table or in a different table.

The order of the columns in the index of a primary index is always that in which the columns appear in the definition of the primary table. In situations where more than one column appears in a primary key, you should consider the types of searches needed. If appropriate, you should switch the order of the columns in the primary table definition so that the most frequently searched for column appears first, or create separate indices, as required, for the other columns.