Page tree
Skip to end of metadata
Go to start of metadata

Purpose

There many internal data structures used by SAP IQ. These internal data structure are often mentioned in varies DBCC command and system stored procedures. The purpose of this content is to provide detail explanation for these internal structures. 

Overview

The key principles of SAP IQ are the concepts of column-wise data structures and bit-wise indexing. Column-wise data structure are easily search without having to scan all rows. Bitmap indexes use bit arrays (commonly called bitmaps) and answer queries by performing bitwise logical operations on these bitmaps. SAP IQ supports 9 different indexes. Each indexes are stored using different internal structures to provide different purposes for query optimization. Each indexe may use one or more different internal structures to contruct the IQ indexes.

IQ Internal Data Structure

These Internal data structures were used by SAP IQ to construct IQ indexes.

      • Blockmap - Logically a flat array indexed by logical block number, physically a tree structure with fixed fanout. Use to translate logical block number to physical block number.

      • Byte Array (B-Array)– Logically a vector of fixed-size cells. Physically a vector of fixed-size pages that partitions cells. It uses blockmap to provide contiguous block numbering.

      • Group Array (G-Array) – Row ids are stored in compact group. Pages are linked. Support sequential and random access to group pages.
      • Bitmap (BM) - Bitmap is an efficient structure for storing and manipulating large sets of row ids, logically a vector of bits, physically a partitioning tree structure with collapsible subtrees. Type of bitmap pages:
        • Range slice – start and end bits relative to partition
        • Bit slice – row ids relative to partition
        • Leaf slice – bit vetor
        • All1s – means entire partitions is all 1s
      • Bitvec – Simple bit vector structure, use for bitmap arithmetic defined in small number.
      • S_UnitList – Structure for storing small variable size structures as unit on a single page.
      • B-Tree – Stores in fixed-size records only. Provides key-based search.

IQ Indexes

IQ indexes are stored using the above internal structures.

      • Fast Projection (Flat FP) – A one-dimensional array, row values stored directly in B-Array.  When IQ UNIQUE has been specified to optimize indexing for certain ranges of cardinality, FP indexes can have a one-byte or two-byte structure.
          • 1-byte, 2-byte FP and 3-byte FP:  Enumerates keys and stores 1-byte or 2-byte enumeration in B-Array in reduce size.

1-byte FP index with one byte lookup table example

          • N-bit:  New in IQ 16, N-bit is a compression scheme that uses n bits to index the dictionary where the data is stored. It replaces FP1, FP2 and FP3.

 

      • High Group (HG)– An enhanced b-tree index to process equality and group by operations on
        high cardinality data.
          • Unique HG: Row ids stores in B-tree.
          • Non-unique HG: Rows ids store in G-Array in key order. B-Tree use to locate the groups.
      • High Non-group (HNG) – A non value-based bitmap index, ideal for most high-cardinality DSS
        operations involving ranges or aggregates. Each bitmap represents a bit of the data width. Same row id exists in multiple bitmaps. The index has one blockmap for every 128 bitmaps. Identity may be very large due to multiple bitmap identities.

      • Low Fast (LF)– A value-based bitmap for processing queries on low-cardinality data.  Index consist of a B-Tree and bitmap for each key. The bitmap stores row ids for a single key.

 

      • Compares (CMP) – Stores the binary comparison (<, >, =, <=, >=, or NE) of any two columns with identical data types, precision, and scale. Bitmapped index that stores the result for comparison of 2 columns from the same table.
      • Word (WD) – Used to index keywords by treating the contents of a CHAR or VARCHAR column as a delimited list. Storage is identical to HG index, differ from HG is that the row id will exist in multiple groups.
      • Date – An index on columns of data type DATE used to process queries involving date quantities. Bitmaps to store day of week, week, quarter, month, day, and year, just like HNG index. Year bitmaps are indexed by B-Tree.
      • Time - An index on columns of data type TIME used to process queries involving
        time quantities. Bitmaps to store hour, minute and second. Just like HNG index.
      • Datetime (DTTM) – An index on columns of data type DATETIME or TIMESTAMP used to process queries involving datetime quantities. Combine Date and Time indexes on full datetime value.
      • LOB
          • Long Varchar: Data stored as chains of 512-byte cells. Space can be wasted if size is not on 512-byte boundaries.  Object ID stored in B-Array.
          • Long Binary:  First page and number of full/partial pages store in B-Array. FP has 2nd blockmap for LOB data. The pointer to the next page stored in LOB blockmap.

Related Content

Related Documents

Index SAP IQ Columns

Use this structure to help you compose your contributions for WIKI and at the same time will ensure spelling and grammar.

  • No labels