If you create a table on Adaptive Server, but do not create a clustered index, the table is stored as a heap, which means the data rows are not stored in any particular order. This section describes how select, insert, delete, and update operations perform on heap tables when there is no “useful” index to aid in retrieving data.
There are very few justifications for heap tables. Most applications perform better with clustered indexes on the tables. However, heap tables work well for small tables that use only a few pages, and for tables where changes are infrequent
Heap tables can be useful for tables that do not require:
Direct access to single, random rows
Ordering of result sets
Heap tables do not work well for queries against most large tables that must return a subset of the table’s rows.
Partitioned heap tables are useful in applications with frequent, large volumes of batch inserts where the overhead of dropping and creating clustered indexes is unacceptable.
Sequential disk access is efficient, especially with large I/O and asynchronous prefetch. However, the entire table must always be scanned to find any value, and this has potentially large impact in the data cache and other queries.
Batch inserts can also perform efficient sequential I/O. However, there is a potential bottleneck on the last page if multiple processes try to insert data concurrently.
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.
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 Chapter 5, “Indexes,” in Performance and Tuning Series: Locking and Concurrency Control.
