Indexes for Data-Warehousing Environments

In addition to conventional (B-tree) indexes, Extended Parallel Server provides the following indexes that you can use to improve ad hoc query performance in data-warehousing environments:

• Bitmap indexes

  A bitmap index is a specialized variation of a B-tree index. You can use a bitmap index to index columns that can contain one of only a few values, such as marital status or gender. For each highly duplicate value, a bitmap index stores a compressed bitmap for each value that the column might contain. With a bitmap index, storage efficiency increases as the distance between rows that contain the same key decreases.

  You can use a bitmap index when both of the following conditions are true:

  • The key values in the index contain many duplicates.
  • More than one column in the table has an index that the optimizer can use to improve performance on a table scan.
• Generalized-key (GK) indexes

  GK indexes allow you to store the result of an expression, selection of a data set, or intersect of data sets from joined tables as a key in a B-tree or bitmap index, which can be useful in specific queries on one or more large tables.

  To create a GK index, all tables involved should be static tables.

To improve indexing efficiency, Extended Parallel Server also supports the following functionality:

• Automatically combine indexes for use in the same table access.

  You can combine multicolumn indexes with single-column indexes.

• Read a table with an access method known as a Skip Scan.

  When it scans rows from a table, the database server only reads rows that the index indicates, and reads rows in the order that they appear in the database. The skip scan access method guarantees that no page is read twice. Pages are read sequentially, not randomly, which reduces I/O resource requirements. The skip scan also reduces CPU requirements because filtering on the index columns is unnecessary.

• Use a hash semi-join to reduce the work to process certain multitable joins.

  A hash semi-join is especially useful with joins that typify queries against a star schema where one large (fact) table is joined with many small (dimension) tables. The hash semi-join can effectively reduce the set of rows as much as possible before the joins begin.

An analysis of the types of queries you anticipate running against your database can help you decide the type of indexes to create. For information about indexes and indexing methods that you can use to improve query performance, see your IBM Informix: Performance Guide.