About indexes and index optimization
General hints
These are some general hints for index optimization:
BLEVEL
This number should be as small as possible. As "0" is impossible, "1" is best. It should not exceed "4".
CLUSTERING_FACTOR
This value is between two extremes:
- ~ # blocks of the table
- Very good: I/O will be minimal
- ~ # rows of the table
- Not so good, but may be "normal". Such a value indicates that the physical order of the index differs extremely from the physical order of the table rows – which is sometimes not avoidable due to the index specification. However, rebuilding the index may lower the clustering factor.
Invisible indexes
They were introduced with Oracle 11g and are very useful to calculate
performance impacts: What effect would it have to drop that index? Or to have a
new one additionally? DROP/CREATE or ALTER INDEX … UNUSABLE/REBUILD are
expensive operations. So here come the alternatives:
CREATE INDEX .. INVISIBLE;
ALTER INDEX myindex INVISIBLE;
Both statements hide indexes from the optimizer – which only considers
invisible indexes when directly pointed to it using a /*+ HINT */ or, to test
it without altering all concerned SQL:
ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
After having figured out which of the invisible indexes should be used, there are two ways to make them permanently visible again:
ALTER INDEX myindex VISIBLE;
ALTER INDEX myindex REBUILD;
Save space from unusable index partitions
There are many reasons why companies store data while never looking into – many of them are government-driven (e.g. taxes, or other legal stuff). As one is already forced to store the data (or unwilling to drop them), what about indexes? Moreover: What about UNUSABLE indexes? They cannot even be used for anything useful, but still occupy space?
No longer so with Oracle 11gR2 (or higher): rendering an index unusable now automatically drops all its segments, retaining only the index definition (in the data dictionary) so it can be rebuilt. As for above "unused data", if stored within partitioned tables, one can simply render the partitions index unusable:
ALTER INDEX sales_01
MODIFY PARTITION p2001 UNUSABLE;
And free space is gained. But what about queries? Won't they fail due to the
unusable index? Nope. The unusable index is dealt with as "non-existent". So if
that specific partition is queried, the optimizer calls for a full-table-scan –
while using the index on other partitions. And if both are affected, it even
generates an execution plan using some UNION ALL to bring the details
together.