我们知道index的key值被删除之后其使用的空间并不会理解释放，只有当这个index block空间完全不被使用之后才可以被再次使用，这也就是index的pctused为0的缘故。有些时候由于表中的数据被大量删除，此时index的leaf block中保留了很多没有用的key 值，不仅浪费了空间，而且再次使用该index时效率可能也极其低下，可以通过对index的监视以及对index结构的有效性验证来判断index是否需要重建，重建时在高可用性系统中当然还需要考虑lock的问题。[@more@]

SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME VARCHAR2(30)

SQL> select count(*)from t;

COUNT(*)
----------
9848

SQL> create index idx_t on t(id);

索引已创建。
SQL> SET autotrace on
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("ID"=1)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 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 autotrace off
SQL> delete from t where id>1;

已删除9847行。

SQL> commit;

提交完成。

SQL> analyze index idx_t validate structure;

索引已分析

SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats where name='IDX_
T';

BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
32 9848 21 9847

SQL> alter index idx_t monitoring usage;

索引已更改。
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES YES

SQL> alter index idx_t nomonitoring usage;

索引已更改。

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T NO YES
SQL> set autotrace on
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

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

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T | 1 | 2 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - filter("ID"=1)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL> alter index idx_t monitoring usage;

索引已更改。

SQL> select /*+ rule */ count(*)from t where id=1;

COUNT(*)
----------
1

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES YES

SQL> set autotrace on
SQL> select /*+ rule */ count(*)from t where id=1;

COUNT(*)
----------
1

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

-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | INDEX RANGE SCAN| IDX_T |
-----------------------------------

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

2 - access("ID"=1)

Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter index idx_t nomonitoring usage;

索引已更改。

SQL>
--================================
SQL> alter index idx_t monitoring usage;

索引已更改。

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES NO

SQL> set autotrace on
SQL> select /*+ no_index(t idx_t) */ count(*)from t where id=1;

COUNT(*)
----------
1

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 2 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=1)

统计信息
----------------------------------------------------------
127 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ no_index(t idx_t) */ count(*)from t where id=1;

COUNT(*)
----------
1

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 2 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=1)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 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 autotrace off
SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES NO

SQL>

我的试验效果不是很理想，当删除了大量数据之后，还是选择使用了index，通过执行计划对比发现使用索引所需要的逻辑读确实比全表扫表还要小。