FP(3) index

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 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.

Behavior changes

There are some differences in the behavior of FP indexes from earlier releases.

Table 6-7: Sybase IQ fast projection (FP) indexes

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.

Table 6-8: Maximum unique values in FP(3)

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

NoteNotes 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.