Virtual Indexes

For tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.


First, we create and populate a table

CREATE TABLE objects_tab AS SELECT * FROM user_objects;

Now we create an primary key on that table. So delete some rows where object_id is null.

DELETE from objects_tab where object_id is null;
COMMIT;

ALTER TABLE objects_tab ADD CONSTRAINT objects_tab_pk PRIMARY KEY (object_id);
EXEC DBMS_STATS.gather_table_stats('EMP', 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

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


If we query the table using a non-indexed column, we see a full table scan

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

If we repeat the query we can see that the virtual index is now used.

SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

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


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF;
SQL> SELECT index_name FROM user_indexes WHERE INDEX_NAME = 'OBJECTS_TAB_OBJECT_NAME_VI';

no rows selected

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

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_OBJECT_NAME_VI

1 rows selected.

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats('EMP', 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Related Links

1. Create INDEX
2. Execution Plan