Creating an index strategy

 

DB2 Universal Database™ for iSeries™ provides two basic means for accessing tables: a table scan and an index-based retrieval. Index-based retrieval is typically more efficient than table scan when less than 20% of the table rows are selected.

There are two kinds of persistent indexes: binary radix tree indexes, which have been available since 1988, and encoded vector indexes (EVIs), which became available in 1998 with V4R2. Both types of indexes are useful in improving performance for certain kinds of queries.

• Binary radix indexes
  A radix index is a multilevel, hybrid tree structure that allows a large number of key values to be stored efficiently while minimizing access times. A key compression algorithm assists in this process. The lowest level of the tree contains the leaf nodes, which contain the address of the rows in the base table that are associated with the key value. The key value is used to quickly navigator to the leaf node with a few simple binary search tests.

• Encoded vector indexes
  An encoded vector index (EVI) is an index object that is used by the query optimizer and database engine to provide fast data access in decision support and query reporting environments.

• Comparing Binary radix indexes and Encoded vector indexes
  DB2^® UDB for iSeries makes indexes a powerful tool.

• Indexes and the optimizer
  Since the iSeries optimizer uses cost based optimization, the more information that the optimizer is given about the rows and columns in the database, the better able the optimizer is to create the best possible (least costly/fastest) access plan for the query. With the information from the indexes, the optimizer can make better choices about how to process the request (local selection, joins, grouping, and ordering).

• Indexing strategy
  There are two approaches to index creation: proactive and reactive. As the name implies proactive index creation involves anticipating which columns will be most often used for selection, joining, grouping and ordering; and then building indexes over those columns. In the reactive approach, indexes are created based on optimizer feedback, query implementation plan, and system performance measurements.

• Coding for effective indexes
  The following topics provide suggestions that will help you to design code which allows DB2 Universal Database for iSeries to take advantage of available indexes:

• Using indexes with sort sequence
  The following sections provide useful information about how indexes work with sort sequence tables.

• Examples of indexes
  The following index examples are provided to help you create effective indexes.

 

Parent topic:

Performance and query optimization