These ideas are derived and quoted from “Best practices for tuning DB2 UDB v8.1 and its databases

has a number of options that allow for significant performance gains:

  • Optimizing for : use if index is on a read-only table.
  • Optimizing for ascending or descending ordering: use to 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: can 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 and , 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 to convert type-1 to type-2 indexes.