IN-list access (ACCESSTYPE='N' or 'IN')

Db2 might choose a type of IN-list access for queries that contain IN predicates.

Introductory concepts
Begin program-specific programming interface information.

Db2 might select either IN-list index scan or IN-list direct table access. Db2 selects which type to use based on the estimated cost of each. Db2 can also use IN-list direct table access for queries that contain more the one IN predicate.

IN-list index scan (ACCESSTYPE='N')

An IN-list index scan is a special case of the matching index scan, in which a single indexable IN predicate is used as a matching equal predicate. You can regard the IN-list index scan as a series of matching index scans with the values in the IN predicate being used for each matching index scan.

The following example has an index on (C1,C2,C3,C4) and might use an IN-list index scan:
SELECT * FROM T
  WHERE C1=1 AND C2 IN (1,2,3)
    AND C3>0 AND C4<100;
The plan table shows MATCHCOLS = 3 and ACCESSTYPE = N. The IN-list scan is performed as the following three matching index scans:
(C1=1,C2=1,C3>0), (C1=1,C2=2,C3>0), (C1=1,C2=3,C3>0)

Parallelism is supported for queries that involve IN-list index access, regardless of whether the IN-list access is for the inner or outer table of the parallel group.

IN-list direct table access (ACCESSTYPE='IN')

IN-list direct table access occurs when Db2 uses in-memory tables to process one or more IN-list predicates as a matching predicates. This access type is indicated in the PLAN_TABLE by ACCESSTYPE='IN'. Db2 supports matching on multiple IN-list predicates if indexes exist on the necessary columns.

For example, Db2 is likely to select IN-list direct table access for the following query:

SELECT *
FROM T1
WHERE T1.C1 IN ('A', 'B', 'C') AND T1.C2 IN (1, 2, 3);
The PLAN_TABLE, contains one row for each IN-list predicate that Db2 processes through in memory tables. For example, the plan table contains the following rows for the above example query:
Table 1. PLAN_TABLE output for IN-list direct table access
QBLOCK
NO
PLAN
NO
 METHOD TNAME
ACCESS
TYPE
MATCH
COLS
ACCESS
NAME
QBLOCK
_TYPE
TABLE
_TYPE
1 1 0 DSNIN002(01) IN 0   SELECT I
1 2 1 DSNIN003(01) IN 0   SELECT I
1 2 1 TI I 1 T1_IX_C1 SELECT T

If the index is selected to access the base table for T1 and T1.C1 IN (A, B, C) is the matching predicate, the PLAN_TABLE shows two rows.

The join type between the in-memory tables is a nested-loop join. Because the access sequence for multiple IN-list predicates is the same as the index key order,Db2 avoids random I/O to access the index as much as possible. When multiple IN-list predicates are applicable to an index, Db2 decides the optimal number of matching predicates.

The first two rows show access to the in-memory tables, and the second row shows access to the base table. Nested-loop joins are used to join the tables.

End program-specific programming interface information.