Oracle 8 Tips   by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse

Understanding Oracle indexes

Oracle indexes are basically a b-tree structure whereby each node in the index tree contains many pointers to other index nodes (Figure 8.5).

Figure 8.5 The basic structure of an Oracle index.

While this index looks like a tree in concept, in physical storage, the index nodes reside in an Oracle data blocks, just like the table rows are stored on data blocks. As such, many index nodes may be read into memory in a single physical I/O.

If we accept the conventional wisdom that database reorganizations (export-import) are not necessary solely for performance reasons, then the next question that comes to mind is when an index might need to be reorganized (dropped and re-created).

As Oracle indexes grow, two things happen. The first is called a split, where a new node is created at the same index level as the existing node. As each level becomes full, the index may spawn, or create a new level to accommodate the new rows. For indexes that have been analyzed with the ANALYSE INDEX command, the following columns are added into the Remote DBA_indexes view: