SQL and Access Paths › Access Paths › Index Access

Index Access

DB2 uses indexes on your tables to perform these actions:

• Access the data based on the predicates in the query
• Avoid a sort in support of the use of the DISTINCT clause
• ORDER BY and GROUP BY clauses
• INTERSECT and EXCEPT processing

DB2 matches the predicates in your queries to the leading key columns of the indexes that are defined against your tables. The MATCHCOLS column of the PLAN_TABLE indicates this match. If the number of columns matched is greater than zero, the index access is considered as a matching index scan. If the number of matched columns is equal to zero, the index access is considered as a non-matching index scan. In a non-matching index scan, all of the key values and their record identifiers (RIDs) are read.

Note: A non-matching index scan is used if DB2 can use the index to avoid a sort when the query contains any of the following clauses:

• ORDER BY
• DISTINCT
• GROUP BY
• NTERSECT
• EXCEPT

The matching predicates on the leading key columns are equal (=) or IN predicates. This match would correspond to an ACCESSTYPE value of either I or N, respectively. The predicate that matches the last index column can be an equal, IN, NOT NULL, or a range predicate (<, <=, >, >=, LIKE, or between).

For example, the following query assumes that the EMPPROJACT DB2 sample table has an index on the PROJNO, ACTNO, EMSTDATE, and EMPNO columns:

SELECT EMENDATE, EMPTIME
FROM   EMPPROJACT
WHERE  PROJNO = 'AD3100'
AND    ACTNO IN (10, 60)
AND    EMSTDATE > '2002-01-01'
AND    EMPNO = 000010

In the previous example, DB2 chooses a matching index scan matching on the PROJNO, ACTNO, and EMSTDATE columns. DB2 does not choose a match on the EMPNO column because the previous predicate is a range predicate. However, the EMPNO column can be applied as an index screening column. Because the EMPNO column is a stage 1 predicate, DB2 can apply it to the index entries after the index is read. Although the EMPNO column does not limit the range of entries that are retrieved from the index, it can eliminate the entries as the index is read. This elimination reduces the number of data rows that have to be retrieved from the table.

If all of the columns specified in a statement can be found in an index, DB2 may choose index only access. The value Y in the INDEXONLY column of the PLAN_TABLE indicates the index only access.

DB2 can access indexes using multi-index access. An ACCESSTYPE of M in the PLAN_TABLE indicates the multi-index access. A multi-index access involves reading multiple indexes or the same index multiple times, gathering qualifying RID values together in a union or intersection of values and then sorting them in data page number sequence. In this way, a table can still be accessed efficiently for more complicated queries.

With multi-index access, each operation is represented in the PLAN_TABLE in an individual row. These steps include the matching index access (ACCESSTYPE = MX or DX) for regular indexes or DOCID XML indexes, and then unions or intersections of the RIDs (ACCESSTYPE = MU, MI, DU, or DI).

For example, the following SQL statement could use multi-index access if an index exists on the LASTNAME and FIRSTNME columns of the EMP table:

SELECT EMPNO
FROM   EMP
WHERE  (LASTNAME = 'HAAS' AND FIRSTNME = 'CHRISTINE')
OR     (LASTNAME = 'CHRISTINE' AND FIRSTNME = 'HAAS')

With the previous query, DB2 could possibly access the index twice, union the resulting RID values together (due to the OR condition in the WHERE clause), and then access the data.