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:
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 | ||
DB2 for I: Binary-Radix Index
IBM i developer.