ACMODE=I in EXPLAIN can mean sorted set
Article ID: 125216
Updated On:
Products
Issue/Introduction
In the output table of an EXPLAIN statement, a value of "I" in column ACMODE can indicate a sorted set, and not necessarily an index.
The EXPLAIN statement can be used on an SQL query to generate an output table which will describe the access path that the statement will use. One of the columns in the EXPLAIN table is ACMODE. A value of "I" in ACMODE is documented as meaning an index is being used in that step.
Environment
Release: All supported releases.
Resolution
A value of "I" in the ACMODE column of an EXPLAIN table can also mean a sorted set. A sorted set is similar to an index in that a symbolic key can be used to locate a member of the set or index. In an index, an internal B-tree structure is used to directly locate the record according to the value of the symbolic key. In a sorted set, the members of the set are sorted in a chain (linked list) according to the value. Therefore, access via a sorted set will typically not be as efficient as it would be with an index.
For example, an EXPLAIN on the following SQL statement:
SELECT E.EMP_ID_0415, X.* FROM EMPNET.EMPLOYEE E
INNER JOIN EMPNET.EXPERTISE X ON "EMP-EXPERTISE"
WHERE EMP_ID_0415=23 AND SKILL_LEVEL_0425='02';
may return (in part) the following:
*+ PSTAMP SECTION COMMAND QBLOCK STEP STYPE PBLOCK
*+ ------ ------- ------- ------ ---- ----- ------
*+ 0 25 1 1 1 0
*+ 0 25 1 2 1 0
*+ 0 25 1 3 2 0
*+
*+ PSTEP TSCHEMA TABLE TSTAMP
*+ ----- ------- ----- ------
*+ 3 EMPNET EMPLOYEE 0001-01-01-00.00.00.000000
*+ 3 EMPNET EXPERTISE 0001-01-01-00.00.00.000000
*+ 0 2019-01-24-07.04.09.242171
*+
*+ ACMODE ACNAME LFS SORTC SORTN SUBQC
*+ ------ ------ --- ----- ----- -----
*+ C
*+ I EMP-EXPERTISE N
*+
Note that the row with ACMODE="I" also specifies ACNAME="EMP-EXPERTISE". EMP-EXPERTISE is not an index, it's a sorted set.
Feedback
