If you create a table on Adaptive Server, but do not create a clustered index, the table is stored as a heap. The data rows are not stored in any particular order. This section describes how select, insert, delete, and update operations perform on heaps when there is no “useful” index to aid in retrieving data.
The phrase “no useful index” is important in describing the optimizer’s decision to perform a table scan. Sometimes, an index exists on the columns named in a where clause, but the optimizer determines that it would be more costly to use the index than to perform a table scan.
Other chapters in this book describe how the optimizer costs queries using indexes and how you can get more information about why the optimizer makes these choices.
Table scans are always used when you select all rows in a table. The only exception is when the query includes only columns that are keys in a nonclustered index.
For more information, see “Index covering”.
The following sections describe how Adaptive Server locates rows when a table has no useful index.
