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 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.
Local indexes – local indexes are an index subtree that indexes only one data partition. They can be partitioned, and they are supported on all types of partitioned tables.
Global indexes – global indexes index span all data partitions in a table. Nonpartitioned, global clustered indexes are supported on round-robin partitioned tables, and nonclustered global indexes are supported on all types of partitioned tables. Global indexes cannot be partitioned.
Local and global indexes are described in Chapter 10, “Partitioning Tables and Indexes.” The remaining types of indexes are described in more detail later in this chapter.
