NBit Dictionary Compression

Continuous NBit dictionary compression replaces 1, 2, 3 byte dictionary compression in the initial 16.0 version. All datatypes except LOB (character and binary) and BIT datatypes may be NBit columns.

NBit dictionary compression provides more efficient compression and greater performance than 1, 2, 3 byte dictionary compression. The IQ UNIQUE column constraint explicitly determines whether the column loads as Flat FP or NBit FP. An IQ UNIQUE n value set to 0 specifies a Flat FP. An n value greater than 0 but less than FP_NBIT_AUTOSIZE_LIMIT creates a NBit column initially sized to n.

Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits defined in the FP_NBIT_AUTOSIZE_LIMIT. 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 auto-size limit.

Note: BIT, BLOB,and CLOB data types do not support NBit dictionary compression. If FP_NBIT_IQ15_COMPATIBILITY=’OFF’, a non-zero IQ UNIQUE column specification in a CREATE TABLE or ALTER TABLE statement that includes these data types returns an error.

NBIT Levels and corresponding IQ UNIQUE cutoffs

SAP Sybase IQ 15 supported three levels of tokenization: FP(1), FP(2) and FP(3). SAP Sybase IQ 16.0 supports 31 possible NBit levels.

NBit Value	IQ UNIQUE
1	2
2	4
3	8
4	16
5	32
6	64
7	128
8	256 ¹
9	512
10	1024
11	2048
12	4096
13	8192
14	16384
15	32768
16	65536 ²
17	131972
18	262144
19	524288
20	1048576
21	2097152
22	4194304
23	8388608
24	16777216 ³
25	33554432
26	67108864
27	134217728
28	268435456
29	536870912
30	1073741824
31	2147483647
¹ Equivalent to FP(1) ² Equivalent to FP(2) ³ Equivalent to FP(3)

Additional Information

See these topics in the SAP Sybase IQ documentation:

Database Migration – Migration > Database Upgrades

Database Administration – Administration: Database > Index Data > Index Types Comparison > Fast Projection (FP) Index

Statements –
- Reference: Statements and Options > SQL Statements > CREATE TABLE Statement
- Reference: Statements and Options > SQL Statements > ALTER TABLE Statement

Database Options –
- Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_AUTOSIZE_LIMIT
- Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_ENFORCE_LIMITS
- Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_IQ15_COMPATIBILITY
- Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_LOOKUP_MB
- Reference: Statements and Options > Database Options > Alphabetical List of Options > FP_NBIT_ROLLOVER_MAX_MB

Stored Procedures –
- Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqrebuildindex Procedure
- Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqcardinality_analysis Procedure
- Reference: Building Blocks, Tables, and Procedures > System Procedures > Alphabetical List of System Stored Procedures > sp_iqindexmetadata Procedure