Improving Retrieval Times on DB2 Indexes

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 SELECT: use PCTFREE 0 if index is on a read-only table.
• Optimizing for ascending or descending ordering: use ALLOW REVERSE SCANS 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: INCLUDE 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 REORG and 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 INDEXES to convert type-1 to type-2 indexes.