Invisible Indexes in Oracle 11gR2

By: Richard Niemiec


Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that might be a bad index. Every time a record is inserted, all of the indexes have to be updated. If the column of an index is updated, the index has to be updated. Deciding which index to drop without causing a slew of full table scans or Cartesian joins for subsequent user queries is difficult, especially when tuning third-party applications where the code cannot be changed.

The solution just might be the invisible index. Oracle allows you to turn off the index (make it invisible), yet continue to maintain the index (during any DML—INSERT/UPDATE/DELETE) in case you need to turn it back on quickly. You can do this by making the index visible or invisible:

  • ALTER INDEX idx1 INVISIBLE;
  • ALTER INDEX idx1 VISIBLE;
  • CREATE INDEX . . . INVISIBLE;

The following query shows the creation of a new invisible index on the DEPTNO column of the EMP table and subsequent query where the index is not seen:

 

0099_001

0100_001

 

 

 

 

 

 

 

 

 

 

I can still force the use of the index if I use a hint. This only worked in the earliest versions of 11g with an INDEX hint; in 11gR2, with a USE_INVISIBLE_INDEXES hint—or by setting the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE:

 

0100_002

 

 

 

 

 

 

 

 

If I make the index visible, I no longer need to use the INDEX hint:

 

0100_003

 

 

 

 

 

 

 

 

I can also use the NO_INDEX hint to turn off an index (before making it invisible) to see if another index (or no other index) will be used, other than the one that I intend to make invisible. In other words, use any index except the one listed in the NO_INDEX hint. Here is an example:

 

0100_004

 

0101_001

 

 

 

 

 

 

 

 

 

I can make the index invisible again at any time:

 

0101_002

 

 

I can check visibility by querying USER_INDEXES or DBA_INDEXES:

 

0101_003

 

 

 

 

 

TIP

By using invisible indexes, you can “turn off” indexes temporarily (make them invisible) to check how queries perform without them. Because invisible indexes continue to be maintained while invisible, turning them back on (making them visible again), if needed, is easy.

 

 

 

 

 

Leave a Reply