IXAND (Index sets intersection) node

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 re-creating 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.

Feedback