Após muitas leituras sobre tuning e performance, achei algo interessante e rápido para postar aqui no site e que possa ser muito util para os programadores e administradores de Banco Oracle.
Preciso melhorar a performance do meu SQL, será que esse indice vai me ajudar?

Uma boa pergunta, pois até então criar um indice em uma tabela sempre há um custo.
Porem como irei saber se aquele indice vai ser útil mesmo sem poder testar na prática?
Uma das novas features do 11g é criar indices invisiveis, não interferindo no ambiente(produção como no exemplo), uhuuuu claro estou dizendo em produção nada em ambiente teste, pois nem todo mundo tem essa maravilha que é um ambiente de teste bom e legal p/ testar sua aplicação.
Ai vem o X da questão, nas versões anteriores(8i, 9i e 10g) não tem nada para nos ajudar?
Isso mesmo lendo um bucado achei o FAKE INDEX ou VIRTUAL INDEX.

Ok, agora vou mostrar com um exemplo simples do seu funcionamento.

1. Lembrando que esta prática serve para o Oracle informar, se o indice a ser criado, realmente será usado pelo otimizador e seu custo.
2. Ao ser criado, estara somente no dicionario de dados e não será criado o indice mesmo, não haverá segmentos (NOSEGMENT).
3. Há um parametro não documentado pela oracle, que faz parte do Oracle Enterprise Manager Tuning Wizard para que esta pratica seja bem sucessida.

Ex.:
Tabela teste com 398698 registros

SQL> DESC teste
Nome                                      Nulo?    Tipo
----------------------------------------- -------- ----------------------------
CAMPO1                                             CHAR(10)
CAMPO2                                    NOT NULL NUMBER(11)
CAMPO3                                             VARCHAR2(4000)
 
SQL> SELECT campo1,count(1) FROM teste
2  GROUP BY campo1;
 
CAMPO1       COUNT(1)
---------- ----------
111111111       57789
444444444       10000
BBBB            10000
CCCCCCCCCC      10000
DDDDDDDDDD      10000
EEEEEEEEEE      10000
FFFFFFFFFF      10000
GGGGGGGGG       10000
HHHHHHHHH       19999
JJJJJJJJJ       10000
RRRRRRRRR       10000
WWWWWWWWW        6551
ddddddddd       10000
hhhhhhhhh       10000
iiiiiiiii       10000
mmmmmmmmm       10000
nnnnnnnnn       10000
ppppppppp       10000
qqqqqqqqq       50000
rrrrrrrrr       10000
uuuuuuuuu       20000
vvvvvvvvv       10000
xxxxxxxxx       64359
yyyyyyyyy       10000

Agora conhecendo a tabela de teste

Código:

SQL> SET autotrace traceonly EXPLAIN
SQL> SELECT * FROM teste WHERE campo1 = 'xxxxxxxxx';
Plano de Execução
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=16612 Bytes=315628)
1    0   TABLE ACCESS (FULL) OF 'TESTE' (Cost=116 Card=16612 Bytes=315628)

Criando o índice
Código:

SQL> CREATE INDEX teste_i01 ON teste(campo1) nosegment;

Agora ver se ele vai usar o indice criado:

SQL> SELECT * FROM teste WHERE campo1 = 'xxxxxxxxx';
Plano de Execução
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=16612 Bytes=315628)
1    0   TABLE ACCESS (FULL) OF 'TESTE' (Cost=116 Card=16612 Bytes=315628)

Agora deves estar se perguntando porque o meu select não utilizou o meu indice?
Bingo agora vamos para o que nos interessa.

SQL> ALTER session SET "_use_nosegment_indexes" = true;
Plano de Execução
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=16612 Bytes=315628)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTE' (Cost=36 Card=16612 Bytes=315628)
2    1     INDEX (RANGE SCAN) OF 'TESTE_I01' (NON-UNIQUE) (Cost=2 Card=16612)

Obs.: Não esqueça de fazer um analyze na tabela.
Para utilizar por definitivo o indice, deve-se dropar o indice virtual e cria-lo sem a clausula NOSEGMENT.

Importante: Como o indice é virtual sem segmento, o tempo de execução não pode ser calculado pois o indice virtual serve somente para ver seu custo quando for criado realmente. No 11g a história muda.

ref.
Oracle 11g Database New Features - 1.2.9.4 Invisible Indexes
Creating an Invisible Indexes
Making an Index Invisible
Dica - Indices Virtuais