This access method requires indexes. The entire index is read, and any selection criteria that references the key columns of the index are applied against the index. The advantage of this method is that the dataspace is only accessed to retrieve rows that satisfy the selection criteria applied against the index. Any additional selection not performed through the index scan-key selection method is performed at the dataspace level.
The index scan-key selection access method can be very expensive if the search condition applies to a large number of rows because:
How the query optimizer selects queries that use this method
Normally, the optimizer would choose to use table scan processing when the search condition applies to a large number of rows. The optimizer only chooses the index scan-key selection method if less than 20% of the keys are selected or if an operation forces the use of an index. Options that might force the use of an index include:
In these cases, the optimizer may choose to create a temporary index rather than use an existing index. When the optimizer creates a temporary index, it uses a 64K page size for primary dials and an 8K page size for secondary dials. An index created using the SQL CREATE INDEX statement uses 64K page size. For indexes that are created using the CRTLF command, or for SQL indexes created before V4R5M0, the index size is normally 16K.
The optimizer also processes as much of the selection as possible while building the temporary index. Nearly all temporary indexes built by the optimizer are select/omit or sparse indexes. Finally, the optimizer can use multiple parallel tasks when creating the index. The page size difference, corresponding performance improvement from swapping in fewer pages, and the ability to use parallel tasks to create the index may be enough to overcome the overhead cost of creating an index. Dataspace selection is used for building of temporary indexes.
If index scan-key selection access method is used because the query specified ordering (an index was required) the query performance might be improved by using the following parameters to allow the ordering to be done with the query sort.
When a query specifies a select/omit index and the optimizer decides to build a temporary index, all of the selection from the select/omit index is put into the temporary index after any applicable selection from the query.