Skip to end of metadata
Go to start of metadata

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:

 

NBIT Value

IQ UNIQUE

 

 

NBIT Value

IQ UNIQUE

 

1

2

 

 

17

131972

 

2

4

 

 

18

262144

 

3

8

 

 

19

524288

 

4

16

 

 

20

1048576

 

5

32

 

 

21

2097152

 

6

64

 

 

22

4194304

 

7

128

 

 

23

8388608

 

8

256

FP(1)

 

24

16777216

FP(3)

9

512

 

 

25

33554432

 

10

1024

 

 

26

67108864

 

11

2048

 

 

27

134217728

 

12

4096

 

 

28

268435456

 

13

8192

 

 

29

536870912

 

14

16384

 

 

30

1073741824

2^30

15

32768

 

 

31

2147483647

2^31-1

16

655536

FP(2)

 

 

>2147483647

Flat FP

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:

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

_________________________________________________________________________________________________________

 

 

  • No labels