前一阵子与别人讨论关于索引的问题，实际上许多索引按照现在的观点都不需要重建。除非几种特殊的情况，大量的删除操作，
或者删除一部分数据，导致一些索引块不会在被插入等情况，写一些例子来说明情况：
1.建立测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
create table t as select rownum id ,lpad('x',100,'x') name from dual connect by level
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(USER,'T',cascade => true);
2.测试：
SQL> column name format a100
SQL> set autot traceonly
SQL> select /*+ index(t i_t_id) */ * from t where id=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 105 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=5000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
691 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--仅仅4个逻辑读。
SQL> set autot off
SQL> delete from t where id=5100;
9900 rows deleted.
SQL> commit ;
Commit complete.
--仅仅保留中间的100条数据。如果这时查询id的min以及max。
SQL> set autot traceonly
SQL> select min(id) from t ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3363318368
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select max(id) from t ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3363318368
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以发现逻辑读都不小，需要12个逻辑读。这个是因为索引的左右两端都删除了索引信息。
--如果查询id=5000并没有什么变化。
SQL> select /*+ index(t i_t_id) */ * from t where id=5000;
.....
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
3.再做一个测试例子：
create table t1 as select * from all_objects ;
create index i_t1_owner_obj_name on t1(owner, object_name);
delete from t1;
commit;
exec dbms_stats.gather_table_stats(USER,'T1',cascade => true);
4.测试
SQL> set autot traceonly
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
31659 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29373 | 4532K| 19304 (1)| 00:03:52 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29373 | 4532K| 19304 (1)| 00:03:52 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 29373 | | 165 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
23032 consistent gets
155 physical reads
0 redo size
3222326 bytes sent via SQL*Net to client
2258 bytes received via SQL*Net from client
160 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31659 rows processed--通过索引查找，仅仅23032个逻辑读，31659条记录，记录数很多。如果删除owner='SYS'的记录
SQL> delete from t1 where wner='SYS';
31659 rows deleted.
SQL> commit ;
Commit complete.
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30941 | 2930K| 21985 (1)| 00:04:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 30941 | 2930K| 21985 (1)| 00:04:24 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 30941 | | 180 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
176 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--可以发现通过索引探查，返回记录为0，逻辑读还有176个逻辑读。why?
3.做一个研究:
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> @10046on 12
Session altered.
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
no rows selected
SQL> @10046off
*** 2013-01-04 15:48:11.994
WAIT #10: nam='SQL*Net message from client' ela= 26120136 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1357285691994416
CLOSE #10:c=0,e=25,dep=0,type=1,tim=1357285691994557
=====================
PARSING IN CURSOR #2 len=71 dep=0 uid=84 ct=3 lid=84 tim=1357285691996164 hv=567281008 ad='b9e7dfa8' sqlid='037kwzhhx01bh'
select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS'
END OF STMT
PARSE #2:c=2000,e=1547,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1461409258,tim=1357285691996158
EXEC #2:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1461409258,tim=1357285691996323
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1357285691996414
WAIT #2: nam='db file scattered read' ela= 102 file#=4 block#=1952 blocks=8 obj#=267947 tim=1357285691996659
WAIT #2: nam='db file scattered read' ela= 73 file#=4 block#=3440 blocks=8 obj#=267947 tim=1357285691996829
WAIT #2: nam='db file scattered read' ela= 87 file#=4 block#=3304 blocks=8 obj#=267947 tim=1357285691996997
WAIT #2: nam='db file scattered read' ela= 85 file#=4 block#=3312 blocks=8 obj#=267947 tim=1357285691997182
WAIT #2: nam='db file scattered read' ela= 85 file#=4 block#=3320 blocks=8 obj#=267947 tim=1357285691997357
WAIT #2: nam='db file scattered read' ela= 82 file#=4 block#=3328 blocks=8 obj#=267947 tim=1357285691997532
WAIT #2: nam='db file scattered read' ela= 73 file#=4 block#=3336 blocks=8 obj#=267947 tim=1357285691997686
WAIT #2: nam='db file scattered read' ela= 88 file#=4 block#=3344 blocks=8 obj#=267947 tim=1357285691997869
WAIT #2: nam='db file scattered read' ela= 90 file#=4 block#=3352 blocks=8 obj#=267947 tim=1357285691998049
WAIT #2: nam='db file scattered read' ela= 92 file#=4 block#=3360 blocks=8 obj#=267947 tim=1357285691998223
WAIT #2: nam='db file scattered read' ela= 98 file#=4 block#=3368 blocks=8 obj#=267947 tim=1357285691998406
WAIT #2: nam='db file scattered read' ela= 90 file#=4 block#=3376 blocks=8 obj#=267947 tim=1357285691998574
WAIT #2: nam='db file scattered read' ela= 93 file#=4 block#=3384 blocks=8 obj#=267947 tim=1357285691998751
WAIT #2: nam='db file scattered read' ela= 94 file#=4 block#=3392 blocks=8 obj#=267947 tim=1357285691998934
WAIT #2: nam='db file scattered read' ela= 99 file#=4 block#=3400 blocks=8 obj#=267947 tim=1357285691999120
WAIT #2: nam='db file scattered read' ela= 100 file#=4 block#=3408 blocks=8 obj#=267947 tim=1357285691999310
WAIT #2: nam='db file scattered read' ela= 96 file#=4 block#=3416 blocks=8 obj#=267947 tim=1357285691999495
WAIT #2: nam='db file scattered read' ela= 96 file#=4 block#=3424 blocks=8 obj#=267947 tim=1357285691999676
WAIT #2: nam='db file scattered read' ela= 97 file#=4 block#=3432 blocks=8 obj#=267947 tim=1357285691999867
WAIT #2: nam='db file scattered read' ela= 101 file#=4 block#=3448 blocks=8 obj#=267947 tim=1357285692000077
WAIT #2: nam='db file scattered read' ela= 100 file#=4 block#=3456 blocks=8 obj#=267947 tim=1357285692000260
WAIT #2: nam='db file scattered read' ela= 88 file#=4 block#=3464 blocks=8 obj#=267947 tim=1357285692000438
WAIT #2: nam='db file scattered read' ela= 103 file#=4 block#=3472 blocks=8 obj#=267947 tim=1357285692000627
WAIT #2: nam='db file scattered read' ela= 99 file#=4 block#=3480 blocks=8 obj#=267947 tim=1357285692000851
FETCH #2:c=3999,e=4452,p=192,cr=176,cu=0,mis=0,r=0,dep=0,og=1,plh=1461409258,tim=1357285692000912
STAT #2 id=1 cnt=0 pid=0 pos=1 bj=267946 p='TABLE ACCESS BY INDEX ROWID T1 (cr=176 pr=192 pw=0 time=0 us cost=21985 size=3001277 card=30941)'
STAT #2 id=2 cnt=0 pid=1 pos=1 bj=267947 p='INDEX RANGE SCAN I_T1_OWNER_OBJ_NAME (cr=176 pr=192 pw=0 time=0 us cost=180 size=0 card=30941)'
SQL> select object_name,object_id,data_object_id,object_type from dba_objects where object_id=267947;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------- --------- -------------- -------------------
I_T1_OWNER_OBJ_NAME 267947 267947 INDEX
--可以发现依旧扫描原来OWNER='SYS'的索引区域。
5.重建索引看看?
SQL> alter index i_t1_owner_obj_name rebuild ;
Index altered.
SQL> set autot traceonly
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30941 | 2930K| 12523 (1)| 00:02:31 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 30941 | 2930K| 12523 (1)| 00:02:31 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 30941 | | 106 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--重建后，逻辑读仅仅2个。当然这些都是很极端的情况，实际上大多数情况索引都不需要重建。