Tips for index scan

When using an index scan access method, the entire index will be paged in to main memory and any selection criteria that reference the key columns of the index are applied to the index. Like the index probe access method, this will still allow the query to subset the number of rows that need to be considered for the table, but the entire index must be scanned in order to accomplish this.

Again like the index probe access method, this access method is generally associated with a separate table probe operation in order to retrieve the table data needed for the selected rows. The paging for the table in this access method is performed synchronously without any pre-fetch scheduling logic.

The index scan access method can be chosen by the optimizer in order to help minimize the number of rows that need to be considered for the table because of the selection criteria or because the index provides some sequencing or collating ability that is required for a different operation as specified in this query.

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.