You may need to adjust the temporary cache size when configuring 3-byte indexes. You can set values using the server startup command line parameter -iqtc or using the sa_server_option system procedure temp_cache_memory_mb option as follows:
CALL sa_server_option('temp_cache_memory_mb', value)
The enumerated FP indexes use a hash object to manage the values represented in the column. The size of the hash object used with a 3-byte FP can get large, depending on the number of distinct values and the width of the column. With a large enough temporary cache allocation, increasing the value of the option HASH_PINNABLE_CACHE_PERCENT to more than the default value of 20 percent can improve performance by allowing the entire hash object to remain in the cache.
To maximize the use of FP(3) indexes, set the FP_LOOKUP_SIZE option to a value larger than the default of 16MB. See Table 6-8 for maximum distinct counts allowed on a column for an FP(3) index. Table 6-8 also contains examples, with values less than 16777216, where a rollover to a flat FP occurs for a smaller unique count than the expected 16777216.
Columns with a 3-byte index require additional cache to load data. Set FP_LOOKUP_SIZE to an appropriate value before loading columns with 3-bytes indexes.
If a scarcity of pinned buffers occurs, Sybase IQ returns a warning in the .iqmsg file, which also contains notification of possible thrashing:
Warning: Hash Insert forced buffer unpinning detected for FP Index Warning: Hash Insert thrashing detected for FP Index
