When the distinct count of column values exceeds 65536, you can create a 3-byte FP index.
The FP(3) index is structurally similar to FP(1) and FP(2) indexes, with the following key differences:
The maximum size of the FP(3) lookup table is 16777216, not 65536.
The FP(3) index buffer storage contains lookup keys of 3 bytes each.
The 3-byte index stores values in a column (column data), as long as the distinct count does not exceed 16777216. Users can create a 3-byte index on columns only if the size of column data is greater than 3 bytes. Therefore, you cannot create an FP(3) index on columns with data types BIT, TINYINT, SMALLINT, CHAR(<=3), VARCHAR(<=3), BINARY(<=3) and VARBINARY(<=3). Sybase IQ also does not support FP(3) indexes for LONG VARCHAR and LONG BINARY data types.
To create an FP(3) index, either the MINIMIZE_STORAGE must be set ON, or the column must have been created with an IQ UNIQUE constraint value between 65537 and 16777216, including these two values. An FP(3) may also be created from an FP(2) index, once the unique count exceeds 65536, but only for data sizes shown in the Sybase IQ fast projection (FP) indexes table below and excluding data types BIT, TINYINT, SMALLINT, CHAR(<=3), VARCHAR(<=3), BINARY(<=3) and VARBINARY(<=3). See the MINIMIZE_STORAGE option in Reference: Statements and Options.
There are some differences in the behavior of FP indexes from versions of Sybase IQ versions earlier than 15.0.
Distinct count |
Column data size = 1 byte |
Column data size = 2 bytes |
Column data size = 3 bytes |
Column data size >3 bytes |
|---|---|---|---|---|
<257 |
FP(1) |
FP(1) |
FP(1) |
FP(1) |
257 - 65536 |
— |
FP(2) |
FP(2) |
FP(2) |
65537-16777216 |
— |
— |
Flat |
FP(3) |
>16777216 |
— |
— |
— |
Flat |
Like FP(1) and FP(2) indexes, the FP(3) index is not supported for columns whose data type is wider than 255 bytes or less than 4 bytes wide.
The creation of an FP(3) index, overflow, or a forced transition to an FP(3) index is permitted only if the space used by the lookup table is less than the current value of the FP_LOOKUP_SIZE option and less than the portion of the main cache specified by the current setting of FP_LOOKUP_SIZE_PPM.
The maximum number of lookup pages used in Sybase IQ is controlled by the FP_LOOKUP_SIZE option and the FP_LOOKUP_SIZE_PPM option, whichever value is smaller. See Reference: Statements and Options.
The following table calculates the maximum number of distinct values that can be supported in an FP(3) index based on the following formula:
FP_LOOKUP_SIZE / (Column-Data size + Cardinality size)
Cardinality size is the space reserved to store cardinality of all individual data in the lookup store. Cardinality size can have a value of either 4 or 8 bytes. In this example, it has a maximum value of 8 bytes.
This table 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.
Column data type width (bytes) |
||||||
FP_LOOKUP_SIZE (MB) |
4 |
8 |
32 |
64 |
128 |
255 |
1 MB |
87381 |
65536 |
26214 |
14563 |
7710 |
3986 |
4 MB |
349525 |
262144 |
104857 |
58254 |
30840 |
15947 |
8 MB |
699050 |
524288 |
209715 |
116508 |
61680 |
31895 |
16 MB |
1398101 |
1048576 |
419430 |
233016 |
123361 |
63791 |
32 MB |
2796202 |
2097152 |
838860 |
466033 |
246723 |
127583 |
64 MB |
5592405 |
4194304 |
1677721 |
932067 |
493447 |
255166 |
128 MB |
11184810 |
8388608 |
3355443 |
1864135 |
986895 |
510333 |
256 MB |
16777216 |
16777216 |
6710886 |
3728270 |
1973790 |
1020667 |
This table is based on the condition that the value of FP_LOOKUP_SIZE is less than or equal to the value of FP_LOOKUP_SIZE_PPM.