This
node represents the scanning of an index.
Node name: IXSCAN
Represents: The
scanning of an index to produce a reduced stream of row IDs. The scanning
can use optional start and 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 via 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.