When the distinct count 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 VARBINARY 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 after rollover from an FP(2) index, once the unique count exceeds 65536, but only for data sizes shown in Table 6-7 and excluding data types BIT, TINYINT, SMALLINT, CHAR(<=3), VARCHAR(<=3), BINARY(<=3) and VARBINARY(<=3). See “Using IQ UNIQUE constraint on columns” in Chapter 9, “Ensuring Data Integrity” in the Sybase IQ System Administration Guide and the “MINIMIZE_STORAGE option” in Chapter 2, “Database Options” in the Sybase IQ Reference Manual.
There are some differences in the behavior of FP indexes from earlier releases.
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 four 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 is lower. See “FP_LOOKUP_SIZE option” and “FP_LOOKUP_SIZE_PPM option” in Chapter 2, “Database Options,”in the Reference: Statements and Options.
Table 6-8 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.
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 |
Notes
The values illustrated in Table 6-8 are estimates for the number of unique values
in a column for the given value of option FP_LOOKUP_SIZE;
actual values may vary. Such variations are possible because counts
can be stored as 4 bytes or 8 bytes.
Table 6-8 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.
