Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). So basically the index will be visible to optimizer only in our sessions. Optimizer running query in other sessions won’t be able to see this index.
Virtual Index in Oracle
Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter “_use_nosegment_indexes” is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Utility of using virtual index is that, suppose we have a table having huge number of rows and it is getting joined with other tables. If we see that optimizer is creating a plan which is costly and SQL tuning advisor suggest us to create an index on a column, then in case of production database we cannot simply create an index and test the changes. We need to make sure that creating that index wont have any negative impact on the execution plan of other queries running in this database.
So there is where we can use virtual index. Here is how virtual index works.
1) Creating a table
SQL> create table test as select * from emp; Table created.
2) Try selecting a value from test table
SQL> select * from test where ename = 'SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| TEST | ----------------------------------
3) Create a virtual Index on test table
SQL> create index test_idx_1 on test(ename) nosegment; Index created.
In order to create a virtual index, we need to give NOSEGMENT at the end of the create index statement.
This will just create a index on the object_name column of test table. But it will not create an index segment in database.
You can check this by querying dba_objects and dba_indexes tables.
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_IDX_1'; no rows selected SQL> col OBJECT_NAME format a30; SQL> select object_name, object_type from dba_objects where object_name = 'TEST_IDX_1'; OBJECT_NAME OBJECT_TYPE --------------- ------------ TEST_IDX_1 INDEX
So, object exists in database, but we don’t have segment for the same.
Now if you try to run the same select command on test table, still optimizer will NOT use virtual index.
SQL> select * from test where ename = 'SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| TEST | ----------------------------------
In order for optimizer to use virtual index, you need to set a parameter called _USE_NOSEGMENT_INDEXES in your session
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true; Session altered.
Once you set this hidden parameter, optimizer will start using the virtual index you created on this table
SQL> select * from test where ename = 'SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 1221747299 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 354 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 354 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_IDX_1 | 46 | | 1 (0)| 00:00:01 | ------------------------------------------------
If you run this query from some other session, it wont use virtual index. you can analyze virtual indexes
SQL> analyze index TEST_IDX_1 compute statistics; Index analyzed.
You CANNOT rebuild a virtual index
SQL> alter index TEST_IDX_1 rebuild; alter index TEST_IDX_1 rebuild * ERROR at line 1: ORA-08114: can not alter a fake index
Finally, if the index doesn’t seem to satisfy your requirement, then you can drop the virtual index.
SQL> drop index TEST_IDX_1
In the next article, we show how to use invisible index.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
