DB2 10.5 for Linux, UNIX, and Windows

IXSCAN

This operator represents the scanning of an index.

Operator name: IXSCAN

Represents: The scanning of an index to produce a reduced stream of row IDs. The scanning can use optional start/stop conditions, or might apply to indexable predicates that reference columns of the index.

This operation is performed to narrow down the set of qualifying row IDs before accessing the base table (based on predicates).

Performance suggestions:

Over time, database updates can cause an index to become fragmented, resulting in more index pages than necessary. This can be corrected by dropping and recreating the index, or reorganizing the index.
When two or more tables are being accessed, access to the inner table through an index can be made more efficient by providing an index on the join column of the outer table.
If statistics are not current, update them using the RUNSTATS command.
In general, index scans are most effective when only a few row IDs qualify. To estimate the number of qualifying row IDs, the optimizer uses the statistics that are available for the columns referenced in predicates. If some values occur more frequently than others, it is important to request distribution statistics by using the WITH DISTRIBUTION clause for the RUNSTATS command. By using the non-uniform distribution statistics, the optimizer can distinguish among frequently and infrequently occurring values.