DB2 10.5 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.

Index access in column-organized tables

The performance of a select, update, or delete operation that affects only one row in a column-organized table can be improved if the table has unique indexes, because the query optimizer can use an index scan instead of a full table scan.

A column-organized table can be accessed by using an index if that access returns no more than one row. Index access does not return more than one row if either of the following conditions is true:

The index is defined as unique, and a predicate that equates to a constant value is applied to each key column in the index.
The FETCH FIRST 1 ROW ONLY clause was specified in an SQL statement, and this option can be applied during the index scan.

Although you cannot explicitly create an index on a column-organized table, primary key or unique key constraints are enforced by unique indexes. You can create primary key or unique key constraints on column-organized tables to improve performance if the following conditions are true:

The table data is truly unique.
Workloads against the table have frequent select, update, or delete operations on unique columns that affect only one row.
No primary key constraints or unique constraints exist on the columns that are being referenced by select, update, or delete operations that affect only one row.

DB2® Cancun Release 10.5.0.4 and later fix packs include index access support for SELECT statements that are run with isolation level CS (cursor stability). Moreover, the performance of an update or delete operation that references exactly one row in one table, where the row is identified by equality predicates that fully qualify a unique constraint, can be improved by using a unique index that supports a primary key or unique key constraint. The time that is needed to update or delete the single row can be improved if the statement meets the following criteria:

No more than one row is produced by the index scan.
No FETCH operation is necessary, meaning that access must be index-only access.
The predicates that qualify the single row can all be applied as start or stop key values for the index search.
The path from the index access to the update or delete operation must be unbroken; that is, the path cannot contain blocking operations such as TEMP, SORT, or HSJN.

For example, assume that unique index PK exists on column C1 and that unique index UK2 exists on columns (C1,C2). The absence of a CTQ operator immediately below DELETE is an indication that this optimization is occurring.

delete from t1
  where c1 = 99

             Rows
            RETURN
            (   1)
             Cost
              I/O
               |
               1
            DELETE
            (   2)
            16.3893
               2
         /----+-----\
        1            1000
     IXSCAN   CO-TABLE: BLUUSER
     (   3)           T1
     9.10425          Q1
        |
        1 
      1000
INDEX: BLUUSER
       UK2
       Q2