Fast index traversal

The eligibility of indexes depends on the key size for the columns of the index.

You can query the Db2 catalog to identify eligible indexes.

Eligibility of unique indexes

The key size for the ordering columns must be 64 bytes or less. FL 508 Columns in the INCLUDE list do not count toward the size limit for the index key size. However, fast index traversal is not used for the columns in the INCLUDE list.

The following example query returns a list of unique indexes that are eligible for fast index traversal.

SELECT * FROM
(
SELECT
SUBSTR(A.CREATOR,1,10) AS TABLE_CREATOR,
SUBSTR(A.NAME,1,10) AS TABLE_NAME,
SUBSTR(B.CREATOR,1,10) AS INDEX_CREATOR,
SUBSTR(B.NAME,1,10) AS INDEX_NAME,
SUM(D.LENGTH
+ CASE D.COLTYPE WHEN 'VARBIN' THEN 2
ELSE 0
END
+ CASE D.NULLS WHEN 'Y' THEN 1
ELSE 0
END) AS INDEX_LENGTH FROM
SYSIBM.SYSTABLES A, SYSIBM.SYSINDEXES B,
SYSIBM.SYSKEYS C, SYSIBM.SYSCOLUMNS D
WHERE A.NAME = B.TBNAME
AND A.CREATOR = B.TBCREATOR
AND B.CREATOR = C.IXCREATOR
AND B.NAME = C.IXNAME
AND A.NAME = D.TBNAME
AND A.CREATOR = D.TBCREATOR
AND C.COLNAME = D.NAME
AND C.ORDERING <> ' '
AND B.OLDEST_VERSION = B.CURRENT_VERSION
AND D.COLTYPE <> 'TIMESTZ'
AND B.DBID > 6
AND B.UNIQUERULE NOT IN ( 'D','N')
GROUP BY A.CREATOR,A.NAME, B.CREATOR, B.NAME
ORDER BY A.CREATOR,A.NAME, B.CREATOR, B.NAME
) FTB_UNIQUE
WHERE FTB_UNIQUE.INDEX_LENGTH <= 64;

Eligibility of non-unique indexes

FL 508 The key size for the columns of the index must be 56 bytes or less.

The following example query returns a list of non-unique indexes that are eligible for fast index traversal.

SELECT * FROM
(
SELECT
SUBSTR(A.CREATOR,1,10) AS TABLE_CREATOR,
SUBSTR(A.NAME,1,10) AS TABLE_NAME,
SUBSTR(B.CREATOR,1,10) AS INDEX_CREATOR,
SUBSTR(B.NAME,1,10) AS INDEX_NAME,
SUM(D.LENGTH
+ CASE D.COLTYPE WHEN 'VARCHAR' THEN 2
WHEN 'VARBIN' THEN 4
ELSE 0
END
+ CASE D.NULLS WHEN 'Y' THEN 1
ELSE 0
END) AS INDEX_LENGTH FROM
SYSIBM.SYSTABLES A, SYSIBM.SYSINDEXES B,
SYSIBM.SYSKEYS C, SYSIBM.SYSCOLUMNS D
WHERE A.NAME = B.TBNAME
AND A.CREATOR = B.TBCREATOR
AND B.CREATOR = C.IXCREATOR
AND B.NAME = C.IXNAME
AND A.NAME = D.TBNAME
AND A.CREATOR = D.TBCREATOR
AND C.COLNAME = D.NAME
AND B.OLDEST_VERSION = B.CURRENT_VERSION
AND D.COLTYPE <> 'TIMESTZ'
AND B.DBID > 6
AND B.UNIQUERULE = 'D'
GROUP BY A.CREATOR,A.NAME, B.CREATOR, B.NAME
ORDER BY A.CREATOR,A.NAME, B.CREATOR, B.NAME
) FTB_NON_UNIQUE
WHERE FTB_NON_UNIQUE.INDEX_LENGTH <= 56;