How Indexes Become Fragmented
The advantages of indexing do not come without a cost. As database objects, indexes are created for tables only and they must be in sync with them: indexes must be updated by the database with every data manipulation language (DML) operationINSERT, DELETE, or UPDATE. When a row is deleted from a table, the Oracle database will not reuse the corresponding index space until you rebuild the index. That's the way indexes are implemented in Oracle. The PCTUSED parameter for indexes is always 0, which means thatunlike table blocksindex blocks will not be put on the free list for reuse.
Therefore, indexes are always growing and can become very fragmented over time, especially in tables with dynamic data. As indexes grow, two things happen: splitting and spawning.
Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally and may degrade performance.
Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes.
Extracting Index Internal Statistics
The USER_INDEXES view contains statistical information that is placed there whenever the ANALYZE INDEX command is issued.
Let's try an examplethe index CUSTOMER_LASTNAME_IND:
analyze index CUSTOMER_LASTNAME_IND compute statistics;
Unfortunately, the USER_INDEXES view is designed to provide information to the Oracle Cost-Based Optimizer (CBO) for SQL execution path determination. It does not keep statistics on the internal status of Oracle indexes. This can be accomplished by using the ANALYZE INDEX command with a VALIDATE STRUCTURE clause. It doesn't change the statistics in the USER_INDEXES view; therefore it could be executed safely without affecting the CBO.
Here is the syntax:
analyze index CUSTOMER_LASTNAME_IND validate structure;
It populates the SYS.INDEX_STATS view only. The SYS.INDEX_STATS view can be accessed with the public synonym INDEX_STATS. The INDEX_STATS view will hold information for one index at a time: it will never contain more than one row. Therefore you need to query this view before you analyze next index.
Let's look at the data inside INDEX_STATS:
select name as IndexName,
Here are the results of that query:
INDEXNAME HEIGHT LF_ROWS DEL_LF_ROWS
------------------------- ---------- ---------- ---------------
CUSTOMER_LASTNAME_IND 2 5237 2130
The following INDEX_STATS columns are especially useful:
- height refers to the maximum number of levels encountered within the index.
- lf_rows refers to the total number of leafs nodes in the index.
- del_lf_rows refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.