Oracle Virtual Index is very useful feature which will help simulate existence of an index and test its impact without actual creating them. This has been especially useful on production system to fix performance issues. Ensure to drop the virtual indexes after the analysis is completed. Oracle stats can be gathered on virtual indexes.
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
CREATE unique INDEX index_name on table_name(col_name) NOSEGMENT /* virtual index */;
Following query will help identify virtual indexes
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
CREATE unique INDEX index_name on table_name(col_name) NOSEGMENT /* virtual index */;
Following query will help identify virtual indexes
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;