Virtual / NOSEGMENT / Fake Indexes

Virtual Indexes (aka NOSEGMENT Indexes or Fake Indexes) are useful to check whether the creation of an Index affects the execution plan with out having to create the actual index. Virtual index is just a definition without a physical index segment so it does not consume disk space. Additionally, virtual index is available to a session only when a session explicitly issues an alter session command; hence it does not affect the normal processing of your system.

• Let’s create a test table and populate

SQL> CREATE TABLE t (n CONSTRAINT t_pk PRIMARY KEY, d)
  2  AS
  3  SELECT rownum, mod(rownum,5)
  4  FROM dual CONNECT BY level <= 10000;

Table created.

• As expected, querying the table on primary key column resulted in index unique scan and on non-indexed column resulted in full table scan.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT * FROM t WHERE n = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680

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

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

   2 - access("N"=5)

SQL> SELECT * FROM t WHERE d = 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2000 | 52000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2000 | 52000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("D"=0)

Note
-----
   - dynamic sampling used for this statement

We can create a fake index by simply adding NOSEGMENT clause to the CREATE INDEX statement. The NOSEGMENT clause specifies the index is virtual. By setting the hidden parameter _use_nosegment_indexes to TRUE; you are instructing the optimizer to use the index, if it is useful.

SQL> CREATE INDEX t_d_virtual_idx ON t(d) NOSEGMENT;

Index created.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SQL> SELECT * FROM t WHERE d = 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3632700478

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |  2000 | 52000 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |  2000 | 52000 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_D_VIRTUAL_IDX |    40 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("D"=0)

Note
-----
   - dynamic sampling used for this statement

The optimizer determined that the virtual index deemed useful; hence, changed the execution plan from full table scan to index range scan.

Fake index is really useful, if you have a large table and would like to check if the new index will be used by the optimizer. Since there is no storage area associated with these indexes, they do not have any other overhead. Some important characteristics of fake index are:

• Virtual indexes are reflected in USER_OBJECTS, DBA_OBJECTS but are not reflected in USER_INDEXES, DBA_INDEXES

SQL> SET AUTOTRACE OFF
SQL> SELECT index_name FROM user_indexes WHERE table_name = 'T';

INDEX_NAME
------------------------------
T_PK

SQL> SELECT object_name FROM user_objects WHERE object_name LIKE 'T%' and object_type = 'INDEX'

OBJECT_NAME
------------------------------
T_D_VIRTUAL_IDX
T_PK

• ALTER INDEX command on a fake index will result in ORA-08114 error

SQL> ALTER INDEX t_d_virtual_idx RENAME TO t_d_virtual_idx2;
ALTER INDEX t_d_virtual_idx RENAME TO t_d_virtual_idx2
*
ERROR at line 1:
ORA-08114: can not alter a fake index

• You can not create a fake index on same column list, but can create a conventional (real) index on same column list.

SQL> CREATE INDEX t_d_virtual_idx3 ON t(d) NOSEGMENT;
CREATE INDEX t_d_virtual_idx3 ON t(d) NOSEGMENT
                                   *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> CREATE INDEX t_d_virtual_idx4 ON t(d);

Index created.

• You can analyze or collect statistics on fake indexes; However, no corresponding statistics will be populated

SQL> EXECUTE DBMS_STATS.gather_index_stats(USER, 't_d_virtual_idx');

PL/SQL procedure successfully completed.