Index access (ACCESSTYPE is 'I', 'IN', 'I1', 'N', 'NR', 'MX', or 'DX')

If the ACCESSTYPE column in the plan table has a value of 'I', 'I1', 'IN', 'N',' 'NR' 'MX', or 'DX', Db2 uses an index to access the table that is named in column TNAME.

Introductory concepts

Begin program-specific programming interface information. The columns ACCESSCREATOR and ACCESSNAME identify the index.

If a nested loop join is used in processing the query, you might see ACCESSTYPE='R', but the value of the PRIMARY_ACCESSTYPE column is T. This indicates that sparse index access is used.

Indexes can provide efficient access to data. In fact, that is the only purpose of non-unique indexes. Unique indexes have the additional purpose of ensuring that key values are unique.

Special cases

  • For dynamic SQL queries, Db2 avoids choosing indexes in which all of the partitions of the index are in a restricted state. If only some partitions are in a restricted state, an index might be chosen, because subsequent access might require only unrestricted partitions to be touched. This behavior allows an efficient index to be available as long as there is a possibility that it could be used successfully. For static queries, Db2 does not consider the state of the index partitions when choosing an index.
  • Db2 might also use sparse index access (ACCESSTYPE='R' and PRIMARY_ACCESSTYPE='T') when processing a nested-loop join.
End program-specific programming interface information.