Oracle之move操作导致索引失效

/*
 结论：很多人想用ALTER TABLE MOVE的方式来降低高水平，结果经常忽略了这个操作会导致索引失效，
 请大家通过下列的试验的回顾，以后多留意这点。
 另外alter table t shrink space; 是否能十全十美呢，请看后面的案例分析
*/ 


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);
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME                     STATUS
------------------------------ ------
IDX_OBJECT_ID                  VALID


set linesize 1000
set autotrace  on
select count(*) from t;
  COUNT(*)
----------
  292740
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |   185   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID |   398K|   185   (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        659  consistent gets
          0  physical reads
          0  redo size
        425  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
delete from t where rownum<=292000;
commit;
set autotrace on 
select count(*) from t;
  COUNT(*)
----------
    740
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |   185   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID |     1 |   185   (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        659  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 move;


select count(*) from t;
执行计划
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   740 |     6   (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                  UNUSABLE


alter index idx_object_id rebuild;
set autotrace on
select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID |   740 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  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
                    
----------------------------------------------------------------------------------------------------------------------------------------------