[DICA]Virtual Index “Fake Index” no banco de dados Oracle

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.
Post Reply
brunors

[DICA]Virtual Index “Fake Index” no banco de dados Oracle

Post by brunors »

Olá Portilho!

Recentemente enfrentei um problema com fake index e abordei o mesmo para questão de aprendizado. Vi que o mesmo pode ser útil em algumas situações para simular a criação de um index original. Com isso, segue a íntegra do post que fiz no meu blog e gostaria de compartilhar:

"O virtual index , também conhecido como “Fake Index”, é um tipo de index que existe no dicionário de dados. Todavia, ao qual não tem nenhum segmento associado à ele. O grande intuito desse index, é ser usado como uma simulação da criação de um index físico (Ajudará na optimização do Oracle cost-based SQL (CBO)). É bastante útil em situações, por exemplo, em que o DBA ou o desenvolvedor precisam saber como a criação de um index irá afetar a performance de um banco de dados, sem construir o index fisicamente. É pouco documentado, além de ser diferente do invisible index que fora implementado na versão 11g , já o “fake” index na 9.2.0.1.

Sendo assim, segue a representação da criação do virtual index. Além da análise de performance utilizando Explain Plan:

– Criação de uma tabela que funcionará como exemplo. A mesma receberá o nome de BlogdoBrunors:

SQL> create table BlogdoBrunors as select * from dba_users;
Table created.

– Selecionando um valor para a tabela.

SQL>select user_id, account_status, profile from BlogdoBrunors where USERNAME=’SCOTT’;
USER_ID ACCOUNT_STATUS PROFILE
———- ——————————– ——————————
67 OPEN DEFAULT

– Efetuando uma análise via Explain plan para a consulta:

SQL> set autotrace traceonly explain
SQL> select * from BlogdoBrunors where USERNAME=’SCOTT’;
Execution Plan
———————————————————-
Plan hash value: 996734408
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2176 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BLOGDOBRUNORS | 1 | 2176 | 2 (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“USERNAME”=’SCOTT’)
Note
—–
- dynamic sampling used for this statement (level=2)

– Criação do index virtual para a tabela BlogdoBrunors para a coluna username (Note que para a criação do index abaixo, a cláusula NOSEGMENT é usada. Isto se refere ao fato de o index em questão não possuir um segmento associado para a sua criação):

SQL> create index index_fake_do_blog_do_brunors on BlogdoBrunors(username) nosegment;
Index created.

– Segue consulta para demonstrar que o objeto existe na base de dados. No entanto, não possui um segmento associado:

SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = ‘BlogdoBrunors’ and index_name = ‘INDEX_FAKE_DO_BLOG_DO_BRUNORS’;
no rows selected

SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = ‘INDEX_FAKE_DO_BLOG_DO_BRUNORS’;
OBJECT_NAME OBJECT_TYPE
——————– ——————-
INDEX_FAKE_DO_BLOG_D INDEX
O_BRUNORS

– Utilizando o Explain plan para a demonstração da utilização do index (Note que o index não está sendo utilizado):

SQL> set autotrace traceonly explain
SQL> select * from BlogdoBrunors where USERNAME=’SCOTT’;
Execution Plan
———————————————————-
Plan hash value: 996734408
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2176 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BLOGDOBRUNORS | 1 | 2176 | 2 (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“USERNAME”=’SCOTT’)
Note
—–
- dynamic sampling used for this statement (level=2)

– Para utilizar o Index Virtual, você deverá utilizar a cláusula _USE_NOSEGMENT_INDEXES na sessão :

SQL> alter session set “_USE_NOSEGMENT_INDEXES” = true;
Session altered.

– Select para a demonstração da utilização do index (Note que o index está sendo utilizado):

SQL> select * from BlogdoBrunors where USERNAME=’SCOTT’;
Execution Plan
———————————————————-
Plan hash value: 3431965156
————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 2176 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BLOGDOBRUNORS | 1 | 2176 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_FAKE_DO_BLOG_DO_BRUNORS | 1 | | 1 (0)| 00:00:01 |
————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“USERNAME”=’SCOTT’)
Note
—–
- dynamic sampling used for this statement (level=2)

Segundo a Oracle , você poderá efetuar as seguintes operações em um index virtual:

You can analyze virtual indexes.
You cannot rebuild a virtual index; it throws an ORA-8114: “User attempted to alter a fake index”
You can drop the index just as a normal index.
SQL> drop index <index_name>;

Para mais informações, acesse as notas da Oracle no MOS : Doc ID 1401046.1 e 813513.1.
"
- See more at: http://brunors.com/virtual-index-fake-i ... os-oracle/

Espero que tenham gostado da dica. :D Se tiver mais um adendo, seria ótimo para questão de aprendizado!
Forte abraço!

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: [DICA]Virtual Index “Fake Index” no banco de dados Oracle

Post by portilho »

Obrigado pela dica!

Post Reply