onstat -I command: Print general statistics about index operations

B-Tree Structure

A b-tree is a common way of organizing index pages into a structure that is self-balancing and efficient for multiple processes to search simultaneously.

In an Informix b-tree each page is called a node, and each node contains one or more index items. Each item is associated with a key value and points to a data row. Nodes are linked to each other, often in multiple directions, and in the abstract the picture formed is one of an upside down tree, with a single root node on top and a layer of leaf nodes on the bottom. In between may be one or more layers of twig nodes.

The number of levels and nodes in the tree depend on the number of rows in the table and the size of the index key. As the number of rows increases or decreases the number of nodes and levels in the tree will grow or shrink accordingly. Use the oncheck -pT command to see the number of levels in an individual index and statistics about each level.

The root node is the parent of the nodes in the level below it. Each of those nodes is the parent of some number of nodes in the level below that, and so on. Because leaf nodes are in the lowest level they cannot be parents.

B-Tree Operations

A b-tree merge operation is one in which two index nodes in the same level are combined into one, with all the index items from the right-hand node being moved onto left-hand node. The empty node is then unlinked from the tree and the page is marked free in the partition’s bitmap.

A b-tree split operation is the opposite of a merge. A new node is added to the right of a nearly-full node and about half of the index items are moved to the new node.

A b-tree shuffle operation balances the number of items between two nodes, moving some from the right-hand node to the left-hand node, without removing either node from the tree. A shuffle operation now occurs only when an index is being externally compressed using the SQL administration API’s index compress argument.

An internal b-tree compress operation predates the externally-triggered index compression feature mentioned above, and is unrelated to it. It is simply an attempted merge operation. It may or may not result in a change to the index structure.

A b-tree copyback operation is an update to the parent node when merges or splits on the level below render one of the parent’s key values out of date.

Whereas a b-tree search moves downward through the tree, a scan moves sideways through one of the levels, normally proceeding from left to right (lower values to higher values). A b-tree scan that moves from right to left is called a reverse scan.

Example output

The output provides an overview of index operations.

Index statistics
search     additem    delitem    retry      rev_scans  rs_retries
60320      75783      8153       6          23         0         

splits     page       slot       root       copyback  
980        938        21         184        0         

compress   merges     shuffles   root      
38         3          0          0   

Output description