Virtual Indexes
from http://oracle-base.com/articles/misc/virtual-indexes.php
The process of tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Creating additional indexes is not without its problems. 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 all_objects;
ALTER TABLE objects_tab ADD (
CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
);
EXEC DBMS_STATS.gather_table_stats(USER, '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 EXP
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 | 92 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 92 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
SQL>
If we query the table using a non-indexed column, we see a full table scan.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 207 (5)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 | 184 | 207 (5)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='USER_TABLES')
SQL>
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
SQL>
If we repeat the previous query we can see the virtual index is not visible to the optimizer.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 207 (5)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 | 184 | 207 (5)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='USER_TABLES')
SQL>
To make the virtual index available we must set the _use_nosegment_indexes parameter.
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session Altered
SQL>
If we repeat the query we can see that the virtual index is now used.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 2 | 184 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='USER_TABLES')
SQL>
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;
INDEX_NAME
------------------------------
OBJECTS_TAB_PK
1 row selected.
SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';
OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_PK
OBJECTS_TAB_OBJECT_NAME_VI
2 rows selected.
SQL>
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.
Above query shows that in the IND$ flags of virtual index is 4096 and for other index is 0. That means we can find out the VIRTUAL INDEX FROM following queries ONLY.
SQL> select index_name from dba_ind_columns 2 minus 3 select index_name from dba_indexes;
AND
SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where flags=4096
SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');
PL/SQL procedure successfully completed.
SQL>
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.
