IXAND
Operator name: IXAND
Represents: The ANDing of the results of multiple index
scans using Dynamic Bitmap techniques. The operator allows ANDed
predicates to be applied to multiple indexes, in order to reduce underlying
table accesses to a minimum.
This operator 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 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.
- 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.
- For star joins, create single-column indexes
for each of the most selective columns in the fact
table and the related dimension tables.