Start of change

Fast index traversal

Fast index traversal (sometimes called fast traverse blocks or FTB) is a process that can improve the performance of random index access.

Db2 continuously monitors activity for indexes that are eligible for fast index traversal. Db2 starts using fast index traversal on the eligible indexes when the indexes exceed internally defined levels of activity. Start of changeDb2 also sometimes stops using fast index traversal for an index and automatically frees it from the cache, such as in the following situations:End of change

Start of change
  • The index is closed.
  • Not enough memory is available and another index takes priority due to higher traverse counts, fewer page splits, or more random access.
  • The index becomes empty, such as when a mass delete occurs.
End of change

Start of changeFast index traversal is also not used for any index with more than 2 million leaf pages.End of change

Index eligibility for fast index traversal

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

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. Start of changeFL 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.End of change

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;
Start of changeEligibility of non-unique indexesEnd of change
Start of changeFL 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;
End of change

Indexes that can benefit most from fast index traversal

Indexes that support heavy read access can benefit most from fast index traversal. In general, indexes that are used for large numbers of insert or delete operations are less likely to benefit. However, indexes on tables with random insert or delete patterns, or indexes with large PCTFREE values might also benefit.

Start of changeYou can use the DISPLAY STATS command with the INDEXTRAVERSECOUNT count to identify the indexes with the most traversals. For more information, see -DISPLAY STATS command (Db2).End of change

Controlling fast index traversal

To control the use of fast index traversal, use the following subsystem parameters:

INDEX_MEMORY_CONTROL
The INDEX_MEMORY_CONTROL subsystem parameter setting enables the use of fast index traversal for the entire Db2 subsystem, for only selected indexes specified in the catalog table SYSIBM.SYSINDEXCONTROL catalog table, or disables the use fast index traversal completely. You can also specify how much memory Db2 allocates for fast index traversal, or let Db2 manage it automatically. For more information, see INDEX MEMORY CONTROL field (INDEX_MEMORY_CONTROL subsystem parameter) and Enabling or disabling fast index traversal at the index level.
Start of changeFTB_NON_UNIQUE_INDEXEnd of change
Start of changeFL 508 The FTB_NON_UNIQUE_INDEX subsystem parameter enables or disables the use of fast index traversal for non-unique indexes. For more information, see FTB NON UNIQUE INDEX field (FTB_NON_UNIQUE_INDEX subsystem parameter).End of change

To enable or disable fast index traversal for particular indexes or index partitions, use catalog table SYSIBM.SYSINDEXCONTROL. See Enabling or disabling fast index traversal at the index level.

Monitoring fast index traversal

To view a snapshot of memory usage for fast index traversal, issue the DISPLAY STATS command:

-DISPLAY STATS(INDEXMEMORYUSAGE)

Begin program-specific programming interface information.

You can also gather statistics on memory usage for fast index traversal by starting the following traces:

Statistics class 8 trace
IFCID 389 records in this trace class contain information about the structures that are used for fast index traversal, including the sizes of those structures.
Performance class 4 trace
Start of changeIFCID 477 records in this trace class contain information about allocation and deallocation of the structures that are used for fast index traversal.End of change

End program-specific programming interface information.

End of change