There are more than one INDEX Type Part 2

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;

Code listing 59-a : Reverse Key Index and Index Range Scan

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;

Code listing 59-b : 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?”

Advertisement