In Part 1 after a brief introduction on default indexing feature B*Tree Indexes in Oracle I mentioned Virtual / NoSegment Indexes.
2- Reverse Key Index and Index Range Scan Problem
Using a reverse index on a column fed by a sequence spreads the location of sequential numbers across a wider range of leaf blocks and the problem of a single hot block is removed because the index entries are stored in reverse order.
These indexes are designed to eliminate index hot spots on insert applications and are excellent for insert performance, but they are limited in that they cannot be used for index range scans. Here is a demostration of this problem;
3- How to disable an index
Especially before a bulk operation on your tables you might prefer to disable triggers, indexes and constraints for performance;
SELECT table_name, index_name, 'ALTER INDEX ' || index_name || ' UNUSABLE;' disable, 'ALTER INDEX ' || index_name || ' REBUILD NOLOGGING;' enable FROM user_indexes WHERE ... SELECT table_name, trigger_name, 'ALTER TRIGGER ' || trigger_name || ' DISABLE;' disable, 'ALTER TRIGGER ' || trigger_name || ' ENABLE;' enable FROM user_triggers WHERE ... SELECT table_name, constraint_name, 'ALTER TRIGGER ' || constraint_name || ' DISABLE;' disable, 'ALTER TRIGGER ' || constraint_name || ' ENABLE;' enable FROM user_constraints WHERE ...
But after migration you may experience some side impacts like dirty data and can not be able to enable some of your constraints. Here is a demostration of how to disable an index;
Continue reading with Part 3
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Refences Used :
“Reverse Key” thread on askTom
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Chapter 2 Designing and Developing for Performance
The Oracle ™ Users’ Co-Operative FAQ : “Why would a reverse index be useful when the leading column of the index is generated from a sequence?”