Purpose

The purpose of this wiki page is to provide some details on SAP IQ 16 NBIT index

Overview

NBIT compression scheme was added as a new feature in SAP IQ 16. It uses n bits to index the dictionary where the data is stored.

Definitions

• Dictionary Table – a structure used to store a single instance of each data cell value Token Count Table (a structure into which the count of a given token is stored).
• NBIT FP –  IQ Fast Projection Index whose distinct data cell values are stored in dictionary table and cell indexes to dictionary table are compressed into NBIT's.
• Auto-sized NBIT FP – an NBIT index that was created without a IQ Unique clause in CREATE or ALTER statements.
• Explicitly sized NBIT FP – an NBIT index created with IQ Unique clause greater than 0 in CREATE or ALTER statement.
• Flat FP – Index in which each non-null cell value is stored in ascending row-id order in either fixed or variable format.

Progression of FP (Fast Progression) indexes over IQ versions

•  SAP IQ 12.x – Is when FP indexes were introduced and decoding occurred immediately in the leaf node.
•  SAP IQ 15.x – FP1, FP2 & FP3 was introduced and decoding is delayed until the actual data values are absolutely required.
•  SAP IQ 16.x – NBIT Dictionary compression is introduced which is a continuous dictionary compression replacing the 1, 2 & 3 byte dictionary compression. 

NBIT index features:

• • SAP IQ 16 supports 31 possible NBIT levels.

  • All data types except LOB and BIT maybe NBIT columns.

  • NBIT indexes increase parallelism when encoding and decoding dictionary compressed pages.

  • One saves on storage because of more compression.

  • Columns without an IQ UNIQUE constraint implicitly load as NBIT up to the limits defined in the FP_NBIT_AUTOSIZE_LIMIT (default value being 1048576).

  • If FP_NBIT_IQ15_COMPATIBILITY = "ON" then database ignores all NBIT related options and the behavior is to have 1, 2 & 3 byte FP indexes.

  • It is advisable to set IQ UNIQUE (0) on unique or primary columns thereby letting the optimizer create Flat FP indexes on these columns. These columns will not benefit from dictionary compression due to lack of repeating values.

NBIT Levels and corresponding IQ UNIQUE cutoffs:

Database Option's that impact NBIT creation:

• • IQ UNIQUE
  • FP_NBIT_IQ15_COMPATOBILITY_MODE
    • Allow value: “ON” or “OFF”
    • Default is “ON”
  • FP_NBIT_AUTOSIZE_LIMIT
    • Allow value: 0 ~ 2,147,483,647
    • Default is 1048576 (NBIT=20)
    • If the value is set to 0, columns without an explicit IQ UNIQUE load with a Flat FP index.
  • FP_NBIT_ROLLOVER_MAX_MB
    • Allow value: 1 ~ 2,147,483,647
    • Default is 16384 (16MB)
  • FP_NBIT_ENFORCE_LIMITS
    • Allow value: “ON” or “OFF”
    • Default is “ON”
  • FP_NBIT_LOOKUP_MB
    • Allow value: 1 ~ 2,147,483,647
    • Default is 65536 (64MB)

Stored Procedure’s that provide NBIT related information:

• • sp_iqindexmetadata
  • sp_iqcolumnmetadata
  • sp_iqindexrebuildwidedata
  • sp_iqrowdensity
  • sp_iqrebuildindex

Database upgrading to IQ 16: 

• Databases upgraded from SAP IQ 15.x have FP_NBIT_IQ15_COMPATIBILITY_MODE option set to "ON" by default.
• In order to take advantage of NBIT compressions on an upgraded database, the following needs to be done:
• Set  FP_NBIT_IQ15_COMPATIBILITY_MODE to "OFF"
• Run sp_iqrebuildindex on existing columns whose declared width is greater than 255 prior to any modification.
• All tokenized FP’s when opened for read-write are automatically converted to NBIT.

Related Content

Related Documents

SAP IQ Index Internal Data Structures

_________________________________________________________________________________________________________