Index Management

The indexes in your database are absolutely the single most important determinant in the performance of the OLTP database. An awful database design that has a good index structure will almost always out-perform a wonderful design that is indexed poorly. The importance of indexes becomes more important as the table sizes get larger.

Indexes seem to be even more of a "black box" than tables. DBAs often do not understand the actual layout of the indexes. Books have been written about different methods of indexing, so the area can be somewhat intimidating. For our purposes, a simple explanation of the indexing mechanism should suffice.

B+tree Indexes

Informix indexes use the b+tree method of indexing. Basically, this is a method that stores the key values and pointers to the rowids of the rows containing that key value. These key/pointer pairs can be searched to retrieve the rowids of matching rows. Using the indexes means that a search has to inspect far fewer pages to find the needed rows than would be necessary if a sequential scan of the entire table were to be needed.

The key/pointer information is stored in fixed-size data structures called nodes. The first node is called the root node. When the table is small, an index could possibly be no larger than this root node. Accompanying the key value will be a pointer that points directly to the rowid of the row. As the table grows larger and more and more key values need to be indexed, the root node may fill up. At this time, additional nodes may be added. This is known as adding a level to the tree. When a level is added to the tree, the root level no longer contains pointers to the actual rowids. Each entry in the root node now points to a new node in the tree.

At the lowest level of the tree, the pointer will point to an actual rowid. The nodes that actually point to the rowids are known as leaf nodes. They are the end point of the search. As additional levels and additional nodes are added to the tree, the index becomes larger and more complex.

Since an index must accommodate change in the table's data, it is possible that some of the nodes will fill up and some will become too empty. The engine attempts to balance and prune the b+tree to maximize the usability of the index. To do so, it occasionally merges adjacent nodes into one, splits full nodes into two, or shuffles key/pointer pairs from one index to another. For the most part, these activities are completely invisible, even to the DBA. However, their tracks can be seen by inspecting the logfiles using the tb/onlog utility. (Be careful with tb/onlog. It will freeze your database while you're looking at logs in use. If you must inspect logs, look at logs on tape, where they're harmless.)

How Indexes are Stored

Pages containing index data are stored within the dbspace that contains the tablespace in OnLine systems. IDS systems allow the options of detaching and fragmenting the indexes and building them in dbspaces chosen by the DBA. Index pages contain nothing but index data and miscellaneous overhead items relating to the index. Each page of index data is a b+tree node.

These pages are not allocated in any particular order. Thus, a dbspace may be composed of random index and data pages. To further complicate matters, the dbspace may contain data from multiple tables and multiple indexes, all thrown into one big pot.

If the data and index pages for a particular table become strewn across the disk, table fragmentation can occur. This fragmentation means that the engine has to work a lot harder to get to the data it wants. As a general rule, avoid this type of fragmentation.