If we want to speed up a SQL statement by changing its explain plan, the first thing we may do is to create index and test if the optimizer would use the index to return the results to you. However,
creating indexes on large database could be very time consuming and resource intensive. Sometimes, it may even take hours to complete.
In Oracle 10g , we can creating a virtual index – an index without actually being created. The virtual index will not take up any disk space in the database. This nice feature help us to create an index quickly and test the explain plan. without hindering the database normal operation.
To use virtual index, we first have to enable the _use_nosegment_indexes parameter in session level. In fact, only sessions with _use_nosegment_indexes=true can see the virtual indexes.
Then, we create the index with NOSEGMENT option.
SQL> create table t as select * from dba_objects;
SQL> alter table t modify object_id not null;
SQL> analyze table t compute statistics;
SQL> select * from t where object_id=111;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 139 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1 | 87 | 139 (1)| 00:00:02 |
--------------------------------------------------------------------------
Then we turn on the virtual index with _use_nosegment_indexe=true.
SQL> alter session set "_use_nosegment_indexes"=true;
SQL> create index t_idx on t(object_id) nosegment;
SQL> select * from t where object_id=111;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
The optimizer seems to be able to make use of the index I_DX to find the row with object_id=111 when we use the trace only option. A closer look on the query statistics and you will find the optimizer execute a full table scan to return result to you.
Note that virtual index will not be found in the dba_indexes dynamic view. However, we can still see it in the dba_objects view.
Filed under:
Oracle