Choosing index page sizes

With the CREATE INDEX statement, you can specify buffer pool sizes of 4 KB, 8 KB, 16 KB, and 32 KB for indexes.

About this task

Compressed indexes can use 8 KB, 16 KB, or 32 KB buffer pools. When you use compressed indexes, more index leaf pages can be stored on a disk. The index leaf pages are compressed down from either 8 KB, 16 KB, or 32 KB to fit into a 4 KB page size. For an 8 KB index page size, the best compression ratio is 2:1. For a 16 KB index page size, the best compression ratio is 4:1, and for a 32 KB page size, the best compression ratio is 8:1.

Indexes that are not compressed can take advantage of the larger page sizes of 8 KB, 16 KB, and 32 KB, without the concern of wasted space in the buffer pool. When index compression is working to its fullest potential for any given buffer pool page size, space is not wasted. However, for any combination of buffer pool page sizes (8 KB, 16 KB, and 32 KB) that are compressed to a 4 KB disk page size, some space in the buffer pool or on the disk will be wasted.

For example, consider the following scenarios for a compressed index that has an 8 KB buffer pool page size:
  • If the index compression is very marginal, only half of the 8 KB page in the buffer pool will be used, because only 4 KB of space on the disk is used.
  • If the index compression is very good (for example, a ratio of 4:1), only half of the 4 KB page on the disk is used.
  • If the index compression is almost exactly 2:1, neither space in the buffer pool nor on the disk is wasted. A full 8 KB page of index keys can be compressed to reside on the disk using only a 4 KB page size.

Procedure

To choose appropriate page sizes for indexes, use the following approaches:

  • Specify larger page sizes for indexes with sequential insert and fetch patterns.
    A larger page size can also yield a larger fanout in index non-leaf pages, which can reduce the number of levels in an index and improve performance.
    Creating an index with a larger page size could also reduce the number of page splits in the index. A reduction in page splits is especially beneficial if the latch contention from the index splits is frequent. For example:
    • Latch class 6 in data sharing
    • Latch class X'46' in IFCID 57 performance trace record in a data sharing environment
    • Latch class X'FE' in IFCID 57 record in a non-data-sharing environment
    It can also lead to better performance for sequential access to the index.
  • Specify smaller pages sizes for indexes with random fetch patterns.

Example

Begin general-use programming interface information. The following example specifies a 16 KB buffer pool for the index being created:
CREATE INDEX INDEX1 ON TABLE1 ( I1 ASC, I2 ASC) BUFFERPOOL BP16K1
You can specify a 4 KB, 8 KB, 16 KB, or 32 KB default buffer pool for indexes in a particular database using the CREATE DATABASE or ALTER DATABASE by using the INDEXBP option as in the following examples:
CREATE DATABASE MYDB INDEXBP BP16K1
ALTER DATABASE MYDB INDEXBP BP16K1

End general-use programming interface information.