如果你想要创建一个很大的索引，但并不想给它分配空间，而是要先确定优化器是否会选择使用该索引，那么在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
