使用NOSEGMENT创建测试索引

如果你想要创建一个很大的索引，但并不想给它分配空间，而是要先确定优化器是否会选择使用该索引，那么在11g中可以使用NOSEGMENT来创建索引就可以先进行测试。如果确定了这个索引是有用的，可以删除该索引，然后使用不包含NOSEGMENT的语句重建它。

SQL> create index idx_emp on emp(employee_id) nosegment;

Index created.

SQL> set autot traceonly;
SQL> select * from APP.EMP t where t.employee_id='105';

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   133 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |   133 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("T"."EMPLOYEE_ID"=105)

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1305  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

SQL> ALTER SESSION SET "_use_nosegment_indexes"=true;  <----session级修改隐含参数_use_nosegment_indexes为true

Session altered.

SQL>  set autot traceonly;
SQL> set lines 900
SQL> select * from APP.EMP t where t.employee_id='105';


Execution Plan
----------------------------------------------------------
Plan hash value: 306890541

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |   133 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("T"."EMPLOYEE_ID"=105)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1305  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed