These ideas are derived and quoted from “Best practices for tuning DB2 UDB v8.1 and its databases”
CREATE INDEX has a number of options that allow for significant performance gains:
- Optimizing for
PCTFREE 0if index is on a read-only table.
- Optimizing for ascending or descending ordering: use
ALLOW REVERSE SCANSto allow for an index to be scanned bi-directionally, which means quicker retrieval of ascending and descending result sets. This has no negative performance impact since the index structure does not change internally to support this feature.
- Optimizing for direct retrieval from Indexed columns:
INCLUDEcan be used to include additional non-indexed columns in the index page to promote index-only access and avoid data page fetches.
- TYPE-2 INDEXES drastically reduce next-key locking, allow for index columns greater than 255 bytes by default, allow for both online
RUNSTATS, and support the new multidimensional clustering ability. All new indexes in v8 will be created as type-2 except when there was already a (pre-migration) type-1 index defined on the table. Use
REORG INDEXESto convert type-1 to type-2 indexes.