IBM FileNet P8, Version 5.2.1            

Maximum index key size

Databases impose a maximum size for index keys, also known as composite indexes.

For Microsoft SQL Server:

The maximum number of fields in an index key is 16.
  • For a queue index, the maximum number of fields is 15; workflow system uses one field.
  • For a roster index, the maximum number of fields is 16.
  • For an event log index, the maximum number of user fields is 14; workflow system adds F_TimeStamp and F_SeqNumber to each index.

The maximum size of an index key is 900 bytes.

To determine the size of a composite index key, Microsoft SQL Server totals all the fixed columns plus the minimums of all the variable columns to determine if the composite index is within the allowable maximum size of an index. However, if there is already data in the table, and if the existing data in a column allows the total bytes of the index to be greater than the maximum size, then the index cannot be created. Also, if after the index is created data is inserted (or updated) that allows the maximum value of the index to exceed the maximum value size, then an error occurs at that time.

For example: You can define an index consisting of column1 with a size of 500 bytes and column2 with a size of 500 bytes. If you put 500 bytes of data in column1 and 500 bytes of data in column2, you get an error. If you put 100 bytes of data into column1 and 100 into column2 there is no error.

For Oracle:

The maximum number of fields in an index key is 32.
  • For a queue index, the maximum number of fields is 31; workflow system uses one field.
  • For a roster index, the maximum number of fields is 32.
  • For an event log index, the maximum number of user fields is 30; workflow system adds F_TimeStamp and F_SeqNumber to each index.

The maximum total size of an index key is 70% of the database block size minus some overhead.

To determine the database data block size in SQLPlus:
  1. Log on as sysdba.
  2. At the SQL>, enter show parameters db_block_size.

To determine the size of an index key, the Oracle server totals the maximum defined sizes of all the variable length fields plus the sizes of all the fixed-length fields.

For DB2®:

The maximum number of fields in an index key is 64.
  • For a queue index, the maximum number of fields is 63; workflow system uses one field.
  • For a roster index, the maximum number of fields is 64.
  • For an event log index, the maximum number of user fields is 62; workflow system adds F_TimeStamp and F_SeqNumber to each index.

The maximum total combined length of a unique index key is 8192.



Last updated: October 2015
bpfcg010.htm

© Copyright IBM Corporation 2015.