ORACLE Performance and Tunning

Virtual Index

To build a virtual index, runs the following statement:

CREATE unique INDEX virt_ind_name on table_name(col_name) NOSEGMENT;

Using virtual indexes

To use the index run the following SQL statement:

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Note that virtual indexes are only used when the cost based optimizer is active.

Detecting virtual indexes

To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):

SELECT index_owner, index_name
  FROM dba_ind_columns
 WHERE index_name NOT LIKE 'BIN$%' 
MINUS
SELECT owner, index_name 
  FROM dba_indexes;

Removing virtual indexes

It is important to drop the index after you're done with it. Here is an example:

DROP INDEX virt_ind_name;

Virtual index
Virtual Indexes
How to rewrite complex sql query running 12 hours

Tuning SQL to invoke nested loops joins
High Volume Nested Loops Joins