IXSCAN

IXSCAN

Operator name: IXSCAN

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

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

For more information, see the section on index scans in the Administration Guide.

Performance suggestions:

• Over time, database updates may 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 via an index may be made more efficient by providing an index on the join column of the outer table.

  For more guidelines about indexes, see Creating appropriate indexes.

• If statistics are not current, update them using the runstats command.
• In general, index scans are most effective when only a few rows qualify. To estimate the number of qualifying rows, 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.