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 9i
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 dba_objects; Table created.
2) Try selecting a value from test table
SQL> select * from test where object_name = 'STANDARD'; 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(object_name) 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 dont 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 object_name = 'STANDARD'; 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 object_name = 'STANDARD'; 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; Index dropped.
Invisible Index in 11g
We have a similar concept in Oracle database 11g called invisible index.
In case of invisible index, we can check if creating a new index is actually going to improve the performance or not. This index will not be visible to any other session and it will not be used by any other existing query run on same table.
SQL>drop table t; Table dropped. SQL>create table t as select * from dba_objects; Table created. SQL>create index t_ind1 on t(object_name) invisible; Index created.
You can also make existing index as invisible using alter index command. You can make existing invisible index as visible. As soon as you make index visible, your existing queries will start using new index.
TDCLTN1>alter index t_ind1 visible; TDCLTN1>alter index t_ind1 invisible;
We have a new column in USER_INDEXES called VISIBILITY. This tells whether an index is visible or not.
SQL>select index_name,VISIBILITY from user_indexes where index_name='T_IND1'; INDEX_NAME VISIBILIT ------------------------------ --------- T_IND1 INVISIBLE
So how does INVISIBLE index works ?
Now that we have created an INVISIBLE index, lets try to run a query on new table T and see if it uses the index.
SQL>explain plan for
2 select * from t where object_name='STANDARD';
Explained.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')
So we can see its doing a full table scan.
In order to use the invisible index, we have a new parameter introduced in 11g – OPTIMIZER_USE_INVISIBLE_INDEXES
This parameter can be set to either TRUE or FALSE
If set to true, we are asking optimizer to use the invisible index if it can make a better plan using that.
Lets try to set this parameter to TRUE and run the same query.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 202 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND1 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')
Now it has used the index and the cost of the query also reduced.
If the index seems to make positive impact on the other query as well, then you can make this index as visible.
Note that if the index is INVISIBLE, then optimizer will ignore the index and 10053 trace will show index as “UNUSABLE”
Difference:
So based on above examples, we can see that the difference is
– In case of virtual index, we dont actually create the index, its just the place holder. In case of invisible index we actually create the index
– In case of virtual index, we can check if the index is getting used or not using explain plan and performance of the query can be mentioned statistically by explain plan. In case of invisible index, we can actually run the query and check the performance benefit.
– We cannot make existing index as virtual, unless we have to drop it and create a no_segment index. We can make any index invisible.
Hope this helps !!
Persistent Storage Solutions 