oracle 虚拟索引

 虚拟索引
  虚拟索引(virtual index)是指没有创建对应的物理实体的索引。虚拟索引的目的，是在不必耗cpu,耗IO以及消耗大量存储空间去实际创建索引的情况，来判断一个索引是否能够对sql优化起到作用。
例如下面一个查询

SQL> create table tt as select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects;

表已创建。

SQL> select count(*) from tt;
     72480

SQL>  explain plan for select * from tt where object_id=54;

已解释。
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 264906180

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   384 |   112   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TT   |     4 |   384 |   112   (1)| 00:00:02 |
--------------------------------------------------------------------------
下面创建一个虚拟索引，来看看优化器是否会使用object_id上的索引，以及优化器的成本将发生怎样的变化：

SQL> alter session set "_use_nosegment_indexes"=TRUE;

会话已更改。

SQL> create index id_index on tt(object_id) nosegment;

索引已创建。
create index语句的nosegment选项表明这个索引时"虚拟索引"没有与之关联的实际索引段，如果优化器认为这个索引有用，参数_use_nosegment_indexes将指示数据库可以在执行计划中使用这些索引。下面我们来看看，如果真的创建此索引，它是否值得使用。

SQL> explain plan for select * from tt where object_id=54;

已解释。

SQL> /
Plan hash value: 911556678

--------------------------------------------------------------------------------
--------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT            |          |     4 |   384 |     5   (0)| 00
:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT       |     4 |   384 |     5   (0)| 00
:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_INDEX |   241 |       |     1   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=54)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择18行。

SQL>
本例中，dbms_xplan显示，这个索引确实被认为有用，优化器估算的成本从112下降到5，如果查询经常出现我们将建议创建此索引

查询虚拟索引：
SQL> SELECT index_owner, index_name
  2  FROM dba_ind_columns
  3  WHERE index_name NOT LIKE 'BIN$%'
  4  MINUS
  5  SELECT owner, index_name
  6  FROM dba_indexes;
SYS                            ID_INDEX
删除虚拟索引
SQL> drop index id_index;

索引已删除。