Creating a forest of trees index

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

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.

To create a forest of trees index:

Choose the columns for the index and determine the number of subtrees to create.
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.

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.