Indexes help Adaptive Server locate data. They speed up data retrieval by pointing to the location of a table column’s data on disk. For example, suppose you need to run frequent queries using the identification numbers of stores in the stores table. To prevent Adaptive Server from having to search through each row in the table—which can be time-consuming if the stores table contains millions of rows—you could create the following index, entitled stor_id_ind:
create index stor_id_ind on stores (stor_id)
The stor_id_ind index goes into effect automatically the next time you query the stor_id column in stores. In other words, indexes are transparent to users. SQL includes no syntax for referring to an index in a query. You can only create or drop indexes from a table; Adaptive Server decides whether to use the indexes for each query submitted for that table. As the data in a table changes over time, Adaptive Server may change the table’s indexes to reflect those changes. Again, these changes are transparent to users; Adaptive Server handles this task on its own.
Adaptive Server supports the following types of indexes:
Composite indexes – these indexes involve more than one column. Use this type of index when two or more columns are best searched as a unit because of their logical relationship.
Unique indexes – these indexes do not permit any two rows in the specified columns to have the same value. Adaptive Server checks for duplicate values when the index is created (if data already exists) and each time data is added.
Clustered or nonclustered indexes – clustered indexes force Adaptive Server to continually sort and re-sort the rows of a table so that their physical order is always the same as their logical (or indexed) order. You can have only one clustered index per table. Nonclustered indexes do not require the physical order of rows to be the same as their indexed order. Each nonclustered index can provide access to the data in a different sort order.
These types of indexes are described in more detail later in this chapter.
