/*  
  结论：alter table t shrink的方式降低表的高水平位，也不会导致索引失效，却无法消除索引的大量空块。
  最终导致虽然索引不失效，查询依然不用索引，具体见案例如下：  
*/

--请结合和alter table t move  降低高水平位导致索引失效的例子放在一起思考

--这里用alter table t shrink的方式降低高水平位，结果避免了索引的失效，不过索引不失效了，是否索引就一定会被用到吗，请看下面的故事。

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table t modify object_id not null;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);         
set linesize 1000
set autotrace  on
select count(*) from t;
set autotrace off
delete from t where rownum<=292000;
commit;
set autotrace on 
select count(*) from t;
alter table t enable row movement;
alter table t shrink space;
select count(*) from t;
执行计划
----------------------------------------------------------
Plan hash value: 2966233522


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   740 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
---奇怪，索引去哪儿？怎么不走索引了？
set autotrace off
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME                     STATUS
------------------------------ -------
IDX_OBJECT_ID                  VALID


set autotrace on 
--原来发现走了，还更慢。
select /*+index(t)*/ count(*) from t;
执行计划
--------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |   675   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE  |               |     1 |            |          |
|   2 |   INDEX FULL SCAN| IDX_OBJECT_ID |   740 |   675   (1)| 00:00:09 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        649  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
--结论，alter table t shrink 不会导致索引失效，但是索引块的高水平无法释放。还是会产生大量的逻辑读。