In 8.1.5 and above it is possible to create virtual indexes

Virtual indexes have a data dictionary definition, but no associated segment

Effectiveness of new indexes can be tested by generating theoretical execution plans

The CBO will consider virtual indexes if the hidden parameter _use_nosegment_indexes is set to true

做测试：

SQL> CREATE TABLE t1 AS

2 SELECT * FROM dba_objects

3 WHERE ROWNUM < 1000;

Table created.

SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;

Table analyzed.

SQL> CREATE INDEX i1 ON t1 (owner, object_name) NOSEGMENT;

Index created.（创建虚拟索引使用NOSEGMENT clause)

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'I1');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> SELECT object_id FROM t1

2 WHERE owner = USER AND object_name = 'T1';

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 21 | 5 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='T1' AND "OWNER"=USER@!)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

15 consistent gets

0 physical reads

0 redo size

278 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

(要使用虚拟索引，需要设置隐含参数_use_nosegment_indexes)

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SQL> SELECT object_id FROM t1

2 WHERE owner = USER AND object_name = 'T1';

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1704772559

------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"=USER@! AND "OBJECT_NAME"='T1')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

15 consistent gets

0 physical reads

0 redo size

278 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed