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.

[ Top of Page | Table of Contents | Glossary | Index ]

[ DB2 List of Books | Search the DB2 Books ]