Databases Reference
In-Depth Information
Setting the
_use_nosegment_indexes
parameter doesn't mean that the database will actually use the
index; after all, the index doesn't really exist. You can use this parameter to check if an execution plan
for a query will use the index or not, as shown in the following example:
Tip
Even after creating a virtual index on a column, you can create a regular index on the same column.
SQL> create index virtual_idx
2 on emp(ename) nosegment;
Index created.
SQL> set autotrace on explain
SQL> alter session set "_use_nosegment_indexes"=true
SQL> /
Session altered.
SQL> select ename from emp where ename='KING';
ENAME
----------
KING
Execution Plan
----------------------------------------------------------
Plan hash value: 1165707112
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| VIRTUAL_IDX | 1 | 5 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"='KING')
SQL>
Once you have completed your testing with the virtual index in place, you can drop it in the
following way:
SQL> drop index virtual_idx;
Index dropped.
SQL>
The proceeding explain plan shows that the optimizer considers the virtual index
virtual_idx
.
While the virtual or fake index feature seems enticing, especially in development environments, it's good
to remember that the optimizer may or may not really use it because the index doesn't have as much
