This
node represents the ANDing of the results of multiple
index scans.
Node name: IXAND
Represents: The
ANDing of the results of multiple index scans using Dynamic Bitmap
techniques. The node allows ANDed predicates to be applied to multiple
indexes, in order to reduce underlying table accesses to a minimum.
This
node is performed to:
- Narrow down the set of rows before
accessing the base table
- AND together predicates applied
to multiple indexes
- AND together the results of semijoins,
used in star joins.
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.
- 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.
- IXAND
can best exploit single column indexes, as start and stop
keys are critical in the use of IXAND.