… a few things about the use of index space to surprise your friends and amaze your colleagues:
- If you use “bigfile” tablespaces for your tables this can result in some indexes becoming more space-efficient than they would be otherwise.
- Creating tables in tablespaces built from multiple datafiles may cause some of their indexes to be less space-efficient than they would otherwise be.
- Unless you store tables in different tablespaces from indexes, rebuilding indexes can allow the indexes that you haven’t rebuilt to become less space-efficient.
- Dropping (or truncating) a table may allow unrelated indexes to become less space-efficient
- Using the partition exchange mechanism for data loading may make some unrelated indexes become less space-efficient.
- Some classes of index will be less space-efficient in a RAC system than they would be in a single-instance system.
- Using the “shrink” option on a table may cause some of its indexes to get bigger.
On the plus side: although there are many activities that contribute to indexes being bigger than they would be if they were newly created, there aren’t many cases where the benefit of recreating them (or restructuring your database) is worth the effort. Note that many “space anomalies” in indexes are transient anyway and are self-correcting over time – given enough time and sufficient use.
Footnote: I am not going to supply an explanation for any of the above phenomena because I don’t think they’re sufficiently important to warrant any action – in general. This note has been published only for the purpose of answering trivia questions and similar entertainments.