Creating a forest of trees index

You use the CREATE INDEX statement with the HASH ON clause to create a forest of trees index.

Before you begin

Prerequisite: Determine whether you need a forest of trees index to reduce performance bottlenecks and contention or to reduce the number of levels in a traditional B-Tree index.

About this task

To create a forest of trees index:

Procedure

  1. Choose the columns for the index and determine the number of subtrees to create.
  2. Create the index by using the CREATE INDEX statement with the HASH ON clause:

    For example, the following command creates a forest of trees index with 100 subtrees (buckets) on the C1 column:

    CREATE INDEX fotidx ON tab(c1) hash on (c1) with 100 buckets

    After you create a forest of trees index, it is enabled.

What to do next

You can monitor onstat -g spi command output to verify that root node contention no longer occurs. If you identify performance bottlenecks that are caused by highly contended spin locks, you can rebuild the forest of trees index with more buckets.