Indíces Virtuais

Acredito que todos já precisaram criar algum indice para teste de performance na consulta, porém isso pode demandar tempo para indexar uma tabela grande sem contar espaço em disco, ou você não sabe os efeitos de criar um indice e afetar outras consultas do seu banco,
Dado esse fato, podemos utilizar os indices virtuais, ou indices sem segmentos, abaixo um exemplo de como funciona:


SQL> create table tab_vindex as (select * from dba_objects where object_id is not null);

Table created.

SQL> ALTER TABLE tab_vindex add constraint pk_tab_vindex PRIMARY KEY (object_id);

Table altered.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tab_vindex', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM tab_vindex where object_id = 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2213869000

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TAB_VINDEX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=100)

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 4059569637

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='TAB_VINDEX')

SQL> CREATE INDEX ix_tab_vindex ON tab_vindex(object_name) NOSEGMENT;

Index created.

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 4059569637

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='TAB_VINDEX')

PARA FUNCIONAR É NECESSÁRIO SETAR O PARAMETRO: _use_nosegment_indexes

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 1624739785

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 2 | 200 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_VINDEX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='TAB_VINDEX')

SQL> SET AUTOTRACE OFF

SQL> SELECT index_name FROM user_indexes;

INDEX_NAME
------------------------------
PK_TAB_VINDEX

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
PK_TAB_VINDEX
IX_TAB_VINDEX

SQL> spool off