Most systems do not need FOT indexes. They are only likely to be justifiable where there are hundreds or thousands of active sessions continually contending for the same index simultaneously. When used unnecessarily, they will increase the work required by the database engine, as will be seen in higher estimated costs in query plans. This may in turn result in the query optimizer not using the intended plan if other indexes exist on the table.
Recreating FOT indexes required by constraints on large tables will most likely involve a significant outage unless complex work-arounds are devised, such as creating and keeping replacement copies of affected tables and their new indexes up-to-date with loopback replication for subsequent swapping over.
There are major related defects, so be prepared to work around these or upgrade to the latest maintenance release. Recent examples are:
- IT35860: Forest of trees index becomes degraded following an in-place alter against columns which are not part of the index
- IT35888: Running oncheck -cI against a forest of trees index takes out a lock against every data row (or page) of the table
The following shell script lists FOT indexes and whether they need recreating because of defect IT35860:
Running that against the test database used for this article produces: