Virtual Indexes 在创建时候产生很少的log不同于普通index，不存在于dba_segments视图，存在于dba_objects视图中，区别于table等segments对象，
在建立Virtual Indexes后在建立的index中包括Virtual Indexes中一样的列 不会引起ORA-01408: 此列列表已编制索引错误;
设置了_use_nosegment_indexes = true后,虚拟index生效;
重要的是可以帮助cbo分析要加入的index是否会引成执行成本的下降，帮助调整人员改index是否建立合理等而不需实际建立真正的index进行测试。

SQL> create table test as select * from dba_objects;

表已创建。

SQL> select name,value from v$sysstat where name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5416032

SQL> create index in_test on test(owner) nosegment;

索引已创建。

SQL> select name,value from v$sysstat where name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5420360

SQL> select 5420360-5416032 from dual;

5420360-5416032
---------------
           4328

SQL> set autotrace traceonly explain
SQL> set wrap off
SQL> select owner,object_name from test where wner='SCOTT';

执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   664 |   156   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     8 |   664 |   156   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement

SQL> alter session set "_use_nosegment_indexes"=true;

会话已更改。

SQL> select owner,object_name from test where wner='SCOTT';

执行计划
----------------------------------------------------------
Plan hash value: 3149231300

--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     8 |   664 |     5   (0)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     8 |   664 |     5   (0)| 00:
|*  2 |   INDEX RANGE SCAN          | IN_TEST |   203 |       |     1   (0)| 00:
--------------------------------------------------------------------------------

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

   2 - access("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement

SQL> set autotrace off
SQL> select name,value from v$sysstat where name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5442924

SQL> create index x_test on test(owner);

索引已创建。

SQL> select name,value from v$sysstat where name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           6457732

SQL> select 6457732-5442924 from dual;

6457732-5442924
---------------
        1014808

SQL> alter index x_test rebuild;

索引已更改。

SQL> alter index in_test rebuild;
alter index in_test rebuild
*
第 1 行出现错误:
ORA-08114: 无法改变假索引

SQL> create index e_test on test(owner);
create index e_test on test(owner)
                            *
第 1 行出现错误:
ORA-01408: 此列列表已索引

SQL> create index e_test on test(owner) nosegment;
create index e_test on test(owner) nosegment
                            *
第 1 行出现错误:
ORA-01408: 此列列表已索引

SQL>  select object_name,object_type,status from dba_objects where object_name='X_TEST';
OBJECT_NAME
--------------------------------------------------------------------------------
X_TEST

SQL> set autotrace traceonly explain
SQL> select owner,object_name from test where wner='SCOTT';

执行计划
----------------------------------------------------------
Plan hash value: 948757101

--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    17 |  1411 |     2   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |    17 |  1411 |     2   (0)| 00:0
|*  2 |   INDEX RANGE SCAN          | X_TEST |    17 |       |     1   (0)| 00:0
--------------------------------------------------------------------------------

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

   2 - access("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement

SQL> drop index x_test;

索引已删除。

SQL> select owner,object_name from test where wner='SCOTT';

执行计划
----------------------------------------------------------
Plan hash value: 3149231300

--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     8 |   664 |     5   (0)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     8 |   664 |     5   (0)| 00:
|*  2 |   INDEX RANGE SCAN          | IN_TEST |   203 |       |     1   (0)| 00:
--------------------------------------------------------------------------------

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

   2 - access("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement