VIRTUAL INDEXING

Virtual indexes are definitions for indexes that are not physically created. The pur-
pose of a virtual index is to determine whether an index would be useful in tun-
ing a SQL statement without having to go through the time-consuming CPU, IO,
and storage intensive process of actually creating the index.

 

For instance, consider the following query:
SQL> EXPLAIN PLAN FOR
2      SELECT * FROM sh.sales WHERE quantity_sold > 10000
3  /
Explained.
SQL> SELECT *
2    FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'))
3  /
--------------------------------------------------
| Id  | Operation           | Name  | Cost (%CPU)|
--------------------------------------------------
|   0 | SELECT STATEMENT    |       |   441  (12)|
|   1 |  PARTITION RANGE ALL|       |   441  (12)|
|   2 |   TABLE ACCESS FULL | SALES |   441  (12)|
-------------------------------------------------
We can create a virtual index to see if the optimizer would choose to use an
index on QUANTITY_SOLD and what the expected change in cost would be:
SQL> ALTER SESSION SET "_use_nosegment_indexes"=TRUE;
Session altered.
SQL> CREATE INDEX sh.sales_vi1 ON sh.sales(quantity_sold) NOSEGMENT;
Index created.
The NOSEGMENT option of CREATE INDEX specifies that the index is
“virtual”—not associated with an actual index segment. The _USE_NOSEGMENT
_INDEXES parameter instructs Oracle to use these indexes in explain plans, if the
optimizer determines that such an index would be useful. Now let’s see if this
index would be used if we create it for real:
SQL> EXPLAIN  PLAN FOR
2      SELECT * FROM sh.sales WHERE quantity_sold > 10000;
Explained.
SQL> SELECT *
2    FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));

---------------------------------------------------------------------
| Id  | Operation                          | Name      | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |     3   (0)|
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES     |     3   (0)|
|   2 |   INDEX RANGE SCAN                 | SALES_VI1 |     2   (0)|
---------------------------------------------------------------------
In the preceding example, DBMS_XPLAN shows that the index was indeed
deemed useful and that the cost estimate reduced from 441 to 3. If queries like
this were going to be common, we’d be well advised to create the index.