DB2 Version 9.7 for Linux, UNIX, and Windows

Types of index access

In some cases, the optimizer might find that all of the data that a query requires can be retrieved from an index on the table. In other cases, the optimizer might use more than one index to access tables. In the case of range-clustered tables, data can be accessed through a "virtual" index, which computes the location of data records.

Index-only access

In some cases, all of the required data can be retrieved from an index without accessing the table. This is known as index-only access. For example, consider the following index definition:

   INDEX IX1:  NAME    ASC,
               DEPT    ASC,
               MGR     DESC,
               SALARY  DESC,
               YEARS   ASC

The following query can be satisfied by accessing only the index, without reading the base table:

   select name, dept, mgr, salary
     from employee
     where name = 'SMITH'

Often, however, required columns do not appear in an index. To retrieve the data from these columns, table rows must be read. To enable the optimizer to choose index-only access, create a unique index with include columns. For example, consider the following index definition:

   create unique index ix1 on employee
     (name asc)
     include (dept, mgr, salary, years)

This index enforces the uniqueness of the NAME column and also stores and maintains data for the DEPT, MGR, SALARY, and YEARS columns. In this way, the following query can be satisfied by accessing only the index:

   select name, dept, mgr, salary
     from employee
     where name = 'SMITH'

Be sure to consider whether the additional storage space and maintenance costs of include columns are justified. If queries that exploit include columns are rarely executed, the costs might not be justified.

Multiple-index access

The optimizer can choose to scan multiple indexes on the same table to satisfy the predicates of a WHERE clause. For example, consider the following two index definitions:

   INDEX IX2:  DEPT    ASC
   INDEX IX3:  JOB     ASC,
               YEARS   ASC

The following predicates can be satisfied by using these two indexes:

   where
     dept = :hv1 or
     (job = :hv2 and
     years >= :hv3)

Scanning index IX2 produces a list of record IDs (RIDs) that satisfy the dept = :hv1 predicate. Scanning index IX3 produces a list of RIDs that satisfy the job = :hv2 and years >= :hv3 predicate. These two lists of RIDs are combined, and duplicates are removed before the table is accessed. This is known as index ORing.

Index ORing can also be used for predicates that are specified by an IN clause, as in the following example:

   where
     dept in (:hv1, :hv2, :hv3)

Although the purpose of index ORing is to eliminate duplicate RIDs, the objective of index ANDing is to find common RIDs. Index ANDing might occur when applications that create multiple indexes on corresponding columns in the same table run a query with multiple AND predicates against that table. Multiple index scans against each indexed column produce values that are hashed to create bitmaps. The second bitmap is used to probe the first bitmap to generate the qualifying rows for the final result set. For example, the following indexes:

   INDEX IX4: SALARY   ASC
   INDEX IX5: COMM     ASC

can be used to resolve the following predicates:

   where
     salary between 20000 and 30000 and
     comm between 1000 and 3000

In this example, scanning index IX4 produces a bitmap that satisfies the salary between 20000 and 30000 predicate. Scanning IX5 and probing the bitmap for IX4 produces a list of qualifying RIDs that satisfy both predicates. This is known as dynamic bitmap ANDing. It occurs only if the table has sufficient cardinality, its columns have sufficient values within the qualifying range, or there is sufficient duplication if equality predicates are used.

To realize the performance benefits of dynamic bitmaps when scanning multiple indexes, it might be necessary to change the value of the sortheap database configuration parameter and the sheapthres database manager configuration parameter. Additional sort heap space is required when dynamic bitmaps are used in access plans. When sheapthres is set to be relatively close to sortheap (that is, less than a factor of two or three times per concurrent query), dynamic bitmaps with multiple index access must work with much less memory than the optimizer anticipated. The solution is to increase the value of sheapthres relative to sortheap.

The optimizer does not combine index ANDing and index ORing when accessing a single table.

Index access in range-clustered tables

Unlike standard tables, a range-clustered table does not require a physical index (like a traditional B-tree index) that maps a key value to a row. Instead, it leverages the sequential nature of the column domain and uses a functional mapping to generate the location of a specific row in a table. In the simplest example of this type of mapping, the first key value in the range is the first row in the table, the second value in the range is the second row in the table, and so on.

The optimizer uses the range-clustered property of the table to generate access plans that are based on a perfectly clustered index whose only cost is computing the range clustering function. The clustering of rows within the table is guaranteed, because range-clustered tables retain their original key value order.