Index

DB2 for I: Index internals.


All the RDBM systems are designed to give the best performance for the read and search operations, even if the system has to make some compromise with other operations like Insert or update.

The index is a major part of this strategy. Basically, the index is a way to arrange the table’s data in such a way that search and fetch operations can return the results very fast. But if you create too many indexes on the table, write operations will slow down.

So, the question here is what does actually index contain and how it arranges that data?

Q: What does an index contain?
A: The index contains the values of the key field and a corresponding reference(RRN) to the base table with some metadata.

Q: How the index arranges the data?
A: DB2 has two ways to manage the Index data or simply say two type of the Indexes data.

1. Binary-Radix Index
2. Encoded Vector Index (EVI)

 

Few more things to look into Derived-key and Sparse indexes

 

More references:

  1. Gateway400: Science and Art of Indexing.pdf
  2. Db2_for_i_indexing_methods_and_strategies.pdf

 

Here is a table from IBM to compare Radix and EVI

Comparison value Binary-Radix Indexes Encoded Vector Indexes
Basic data structure A wide, flat tree A Symbol Table and a vector
Interface for creating Command, SQL, System i® Navigator SQL, System i Navigator
Can be created in parallel Yes Yes
Can be maintained in parallel Yes Yes
Used for statistics Yes Yes
Used for selection Yes Yes, with dynamic bitmaps or RRN list
Used for joining Yes Yes (with a hash table)
Used for grouping Yes Yes
Used for ordering Yes No
Used to enforce unique Referential Integrity constraints Yes No
Source for predetermined or ready-made numeric aggregate results No Yes, with INCLUDE keyword option on create
Table 1. Comparison of radix and EVI indexes

IBM i developer.

View Comments
There are currently no comments.