Index Virtual (Fake Index): Simulação de Criação de Index no Banco de Dados Oracle

Virtual Index “Fake Index” no banco de dados Oracle

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.