Oracle 8 Tips
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 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: