By default, the database engine automatically creates a Fast Projection
(FP ) index on all columns. FP indexes optimize projections and allow the database engine to evaluate
certain kinds of search conditions.
An FP index is an array of n fixed-length entries where n is the number of rows in the table. The IQ UNIQUE storage directive
applied to the column determine whether the column loads as a flat FP
or NBit. Flat FP indexes contain actual column cell
values. NBit is a compression scheme that uses n
bits to index the dictionary where the data is stored. All datatypes except
LOB (both character and binary) and BIT datatypes
may be NBit columns.
IQ UNIQUE
IQ UNIQUE defines the expected cardinality of a column and
determines whether the column loads as Flat FP or NBit. An IQ UNIQUE(
n) value explicitly set to 0 loads the column as Flat
FP. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits
defined by the FP_NBIT_AUTOSIZE_LIMIT and FP_NBIT_LOOKUP_MB options:
- FP_NBIT_AUTOSIZE_LIMIT limits the number of distinct values that load as
NBit
- FP_NBIT_LOOKUP_MB sets a threshold for the total NBit dictionary size
- FP_NBIT_ROLLOVER_MAX_MB sets the dictionary size for implicit NBit
rollovers from NBit to Flat FP
- FP_NBIT_ENFORCE_LIMITS enforces NBit dictionary sizing limits. This option
is OFF by default
Using IQ UNIQUE with an
n value less than
the FP_NBIT_AUTOSIZE_LIMIT is not necessary. Auto-size functionality automatically
sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where
you want to load the column as Flat FP or when you want to load a column as NBit
when the number of distinct values exceeds the FP_NBIT_AUTOSIZE_LIMIT.
Rebuilding FP Column Indexes
sp_iqindexmetadata displays
details about column indexes, including the FP
index type (Flat FP or NBIT), the distinct counts, IQ UNIQUE
n value, and dictionary size.
sp_iqrebuildindex rebuilds Flat FP as
NBIT, or NBIT as Flat FP.
The index_clause can reset IQ UNIQUE
n to an explicit value from '0' (to recast an
NBIT column to Flat FP) up to the limits
defined in the FP_NBIT_AUTOSIZE_LIMIT and
FP_NBIT_LOOKUP_MB options. If the count exceeds the
n value, and FP_NBIT_ENFORCE_LIMITS=ON, the
operation rolls back. If the FP_NBIT_ENFORCE_LIMITS=OFF (default),
the NBIT dictionary continues to grow.
Additional Information
- Reference: Statements and Options > Database Options > Alphabetical List of
Options > FP_NBIT_AUTO_LIMIT
- Reference: Statements and Options > Database Options > Alphabetical List of
Options > FP_NBIT_LOOKUP_MB
- RReference: Statements and Options > Database Options > Alphabetical List of
Options > FP_NBIT_ROLLOVER_MAX_MB
- Reference: Statements and Options > Database Options > Alphabetical List of
Options > FP_NBIT_ENFORCE_LIMIT
- Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of
System Stored Procedures > sp_iqrebuildindex
- Reference: Building Blocks, Tables, and Procedures > System Procedures ยป Alphabetical List of System
Stored Procedures > sp_iqindexmetadata