To use system resources efficiently, DB2 Universal Database Version 6 can perform online index reorganization. The database manager uses a B+ tree structure for storing indexes where the bottom level consists of leaf nodes. The leaf nodes, or pages, are where the actual index key values are stored. After considerable delete and update activity, many leaf pages of an index may have only a few index keys left on them.
Without online reorganization, you can only reclaim space through an offline reorganization of the data and index. Use online index reorganization to enable DB2 to dynamically reorganize the pages when database activity creates excess space in the pages. To enable DB2 to perform online reorganization for a specific index, specify the MINPCTUSED option when you create the index with the CREATE INDEX statement.
For more information on online index reorganization , refer to the Administration Guide, Design and Implementation . For more information on the CREATE INDEX statement, refer to the SQL Reference .
DB2 Universal Database Version 6 enables you to create an index that allows both forward and reverse scans. These indexes can improve the performance of your database by eliminating the need for the optimizer to create a temporary table for a reverse scan. To create an index that allows scans in both directions, specify ALLOW REVERSE SCANS in the CREATE INDEX statement. To convert an existing index to one that allows forward and reverse scans, you must drop the existing index and recreate it, using ALLOW REVERSE SCANS in the CREATE INDEX statement.
For more information on forward and reverse scan indexes, refer to the SQL Reference .
To help improve the performance of your databases, DB2 Universal Database Version 6 increases the maximum length of index keys to 1024 bytes. The index key can be composed of up to 16 columns.
For more information on index keys, refer to the Administration Guide, Design and Implementation .
To improve the query performance for decision support databases, and other databases that use a Star Schema design, the DB2 Universal Database Version 6 optimizer takes better advantage of the star join plan. A Star Schema database keeps the bulk of the raw data in a single large table with many columns. A star join is a strategy the optimizer can use to join multiple tables. DB2 automatically uses a star join if the estimated cost of that strategy is less than the estimated cost of other strategies.
For more information on Star Schema databases or star joins, refer to the Administration Guide, Performance .
DB2 Universal Database Version 6 allows you to select the page size that DB2 uses internally to store data on disk. You can select from a 4K, 8K, 16K, or 32K page size. Using a larger page size can:
If your database typically accesses a considerable quantity of contiguous
data that can appear on a single page, increasing the page size can increase
your database performance. Also, the maximum number of columns in a
table and the maximum byte size of a row in a table are determined by the page
size, illustrated in the following table:
Table 2. Limits for Number of Columns and Row Size in Each Table Space Page Size
| Page Size | Row Size Limit | Column Count Limit |
|---|---|---|
| 4K | 4 005 | 500 |
| 8K | 8 101 | 1 012 |
| 16K | 16 239 | 1 012 |
| 32K | 32 677 | 1 012 |
For more information on page size, refer to the description of CREATE TABLE in the SQL Reference .