Skip to main content

Restrictions on Bitmap Indices

Restrictions on Bitmap Indices

Bitmap indices have the following restrictions:

  • You can only define a bitmap index for a field in a record that has a positive integer value ID. Therefore, you can only define bitmap indices in tables (classes) that either use system-assigned numeric ID values, or use an IdKey to define custom ID values when the IdKey is based on a single property with type %Integer and MINVAL > 0, or type %Numeric with SCALE = 0 and MINVAL > 0.

    You can use the $SYSTEM.SQL.SetBitmapFriendlyCheck()Opens in a new tab method to set a system-wide configuration parameter to check at compile time for this restriction, determining whether a defined bitmap index is allowed in a %CacheSQLStorage class. This check only applies to classes that use %CacheSQLStorage. You can use $SYSTEM.SQL.GetBitmapFriendlyCheck()Opens in a new tab to determine the current configuration of this option.

  • You cannot define a bitmap index for a field in a record that uses a multi-field ID key.

  • You cannot define a bitmap index on a UNIQUE column.

  • You cannot use bitmap indices on child tables within parent-child relationships.

  • You cannot store data values within a bitmap index.

For a table containing more than 1 million records, a bitmap index is less efficient than a standard index when the number of unique values exceeds 10,000. Therefore, for a large table it is recommended that you avoid using a bitmap index for any field that contains (or is likely to contain) more than 10,000 unique values; for a table of any size, avoid using a bitmap index for any field that is likely to contain more than 20,000 unique values. These are general approximations, not exact numbers.

Application Logic Restrictions

A bitmap structure can be represented by an array of bit strings, where each element of the array represents a "chunk" with a fixed number of bits. Because undefined is equivalent to a chunk with all 0 bits, the array can be sparse. An array element that represents a chunk of all 0 bits need not exist at all. For this reason, application logic should avoid depending on the $BITCOUNT(str,0) count of 0-valued bits.

Because a bit string contains internal formatting, application logic should never depend upon the physical length of a bit string or upon equating two bit strings that have the same bit values. Following a rollback operation, a bit string is restored to its bit values prior to the transaction. However, because of internal formatting, the rolled back bit string may not equate to or have the same physical length as the bit string prior to the transaction.

FeedbackOpens in a new tab