Indexes that are padded or not padded

The NOT PADDED and PADDED options of the CREATE INDEX and ALTER INDEX statements specify how varying-length string columns are stored in an index.

Begin general-use programming interface information.

You can choose not to pad varying-length string columns in the index to their maximum length (the default), or you can choose to pad them.

If you specify the NOT PADDED clause on a CREATE INDEX statement, any varying-length columns in the index key are not padded to their maximum length. If an existing index key includes varying-length columns, you can consider altering the index to use the NOT PADDED clause. However, using the NOT PADDED clause on the ALTER INDEX statement to change the padding places the index in the REBUILD-pending (RBDP) state. You should rebuild the index to remove the RBDP state.

End general-use programming interface information.

The PADIX subsystem parameter controls whether varying length columns are padded by default. For more information, see PAD INDEXES BY DEFAULT field (PADIX subsystem parameter).

Padded indexes prevent index-only access to data in the following situations:

  • Varying-length data is returned
  • A VARCHAR column has a LIKE predicate
  • Start of changeAn index on expression is the left-hand side of a LIKE predicate, and the index does not contain varying length data.End of change
Tip: If your application typically accesses varying-length columns, use the NOT PADDED clause to implement index-only access.

Using the NOT PADDED clause has the following advantages:

  • Db2 can use index-only access for the varying-length columns within the index key, which enhances performance.
  • Db2 stores only actual data, which reduces the storage requirements for the index key.

However, using the NOT PADDED clause might also have the following disadvantages:

  • Index key comparisons are slower because Db2 must compare each pair of corresponding varying-length columns individually instead of comparing the entire key when the columns are padded to their maximum length.
  • Db2 stores an additional 2-byte length field for each varying-length column. Therefore, if the length of the padding (to the maximum length) is less than or equal to 2 bytes, the storage requirements could actually be greater for varying-length columns that are not padded.