NBit Dictionary Compression

Continuous NBit dictionary compression replaces 1, 2, 3 byte dictionary compression. 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