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 operation
allows ANDed predicates to be applied to multiple indexes, in order
to reduce underlying table accesses to a minimum.
This operation 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.
This operation can best exploit single column indexes, as start
and stop keys are critical to it.