Oracle Virtual Index

Oracle has the undocumented feature which helps to test if a specific index creation will be used by a query to improve performance during a tuning exercise. This is a fake index hence its very fast to do the initial test and also does not occupy space, given below is a very simple example on testing the virtual index.



1.Create a test table for the activity.

create table test as select * from dba_objects;

2.Collect the statistics for the table

EXEC DBMS_STATS.gather_table_stats(user, 'TEST')

3.Check the plan nfor the query

explain plan for
select * from test where object_id=200;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 196 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 196 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

1 - filter("OBJECT_ID"=200)

13 rows selected.

4.Create a virtuala index with nosegment clause.

create index test_idx1 on test(object_id) nosegment;

5.Alter session to use the virtual index

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

6.Collect statistics for the table
Note: The statistics will not be populated for the index.

EXEC DBMS_STATS.gather_table_stats(user, 'TEST', cascade => TRUE);


7.Check the virtual index exists
Note: The information is not populated in DBA_INDEXES but in DBA_OBJECTS

SQL> SELECT o.object_name AS fake_index_name FROM user_objects o
WHERE o.object_type = 'INDEX' AND NOT EXISTS
(SELECT null FROM user_indexes i WHERE o.object_name = i.index_name );

FAKE_INDEX_NAME
------------------------------------------------------------------------
TEST_IDX1


8.Check the plan to see if the index is used.

explain plan for
select * from test where object_id=200;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2624864549

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=200)

14 rows selected.


9.Drop the virtual index and create a real one if it helps.

drop index test_idx1;