This chapter describes how Adaptive Server stores indexes and uses them to speed data retrieval for select, update, delete, and insert operations.
Indexes are the most important physical design element in improving database performance:
Indexes help to avoid table scans. A few index pages and data pages can satisfy many queries without requiring reads on hundreds of data pages.
For some queries, data can be retrieved from a nonclustered index without accessing data rows.
Clustered indexes can randomize data inserts, avoiding insert hot spots on the last page of a table.
Indexes can help to avoid sorts, if the index order matches the order of the columns in an order by clause.
For most partitioned tables, you can create global indexes with one index tree to cover the whole table, or you can create local indexes with multiple index trees, each of which covers one partition of the table.
In addition to their performance benefits, indexes can enforce the uniqueness of data.
Indexes are database objects created on a table to speed direct access to specific data rows. Indexes store the values of the keys named when the index was created and logical pointers to the data pages or to other index pages.
Although indexes speed data retrieval, they can slow down data modifications, since most changes to the data require index updates. Optimal indexing demands an understanding of:
The behavior of queries that access unindexed heap tables, tables with clustered indexes, and tables with nonclustered indexes
The mix of queries that run on your server
The relative benefits of local and global indexes on partitioned tables
The Adaptive Server optimizer
