Oracle 10g在线整理碎片索引是否失效

从Oracle 10g开始，Oracle推出了表格在线整理碎片的功能（An ALTER TABLE ... SHRINK ），极大的提高了系统可用性。
那么表格碎片整理之后，索引是否会失效呢？实验结果来证明：

SQL> CREATE TABLE test_rowid (
  2  id NUMBER,
  3  pad VARCHAR2(4000),
  4  CONSTRAINT test_rowid_pk PRIMARY KEY (id)
  5  );
 

Table created.


SQL> INSERT INTO test_rowid
  2  SELECT rownum AS id, dbms_random.string('p',500) AS pad
  3  FROM dual
  4  CONNECT BY level <= 200000;


200000 rows created.

SQL> create index idx_pad on test_rowid(pad);

Index created.

SQL> select sum(blocks) from dba_extents where owner='ZHOUL' and segment_name='TEST_ROWID';

SUM(BLOCKS)
-----------
      15360
     

SQL> create table test_rowid_old as  select rowid "row_id",id,pad from test_rowid;

Table created.


SQL> delete from test_rowid where id>=100000 and id<150000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select sum(blocks) from dba_extents where owner='ZHOUL' and segment_name='TEST_ROWID';

SUM(BLOCKS)
-----------
      15360

SQL> alter table test_rowid enable row movement;     

Table altered.

SQL> ALTER TABLE test_rowid SHRINK space cascade;


Table altered.

SQL> select sum(blocks) from dba_extents where owner='ZHOUL' and segment_name='TEST_ROWID';

SUM(BLOCKS)
-----------
      10864
     
SQL> create table test_rowid_new as  select rowid "row_id",id,pad from test_rowid;

Table created.

SQL> select t1."row_id",t2."row_id",t1.id,t2.id from test_rowid_old t1,test_rowid_new t2 where t1."row_id"!=t2."row_id" and t1.id=t2.id and rownum<10;


row_id             row_id                     ID         ID
------------------ ------------------ ---------- ----------
AAF0CfAAFAAAE53AAA AAF0CfAAFAAAE5tAAE     151929     151929
AAF0CfAAFAAAE53AAB AAF0CfAAFAAAE5tAAF     151930     151930
AAF0CfAAFAAAE53AAC AAF0CfAAFAAAE5tAAG     151931     151931
AAF0CfAAFAAAE53AAD AAF0CfAAFAAAE5tAAH     151932     151932
AAF0CfAAFAAAE53AAE AAF0CfAAFAAAE5tAAI     151933     151933
AAF0CfAAFAAAE53AAF AAF0CfAAFAAAE5tAAJ     151934     151934
AAF0CfAAFAAAE53AAG AAF0CfAAFAAAE5tAAK     151935     151935
AAF0CfAAFAAAE53AAH AAF0CfAAFAAAE5tAAL     151936     151936
AAF0CfAAFAAAE53AAI AAF0CfAAFAAAE5tAAM     151937     151937

9 rows selected.

SQL> select INDEX_NAME,STATUS from  dba_indexes where owner='ZHOUL' and lower(INDEX_NAME)='test_rowid_pk';

INDEX_NAME                     STATUS
------------------------------ --------
TEST_ROWID_PK                  VALID


SQL> select INDEX_NAME,STATUS from  dba_indexes where owner='ZHOUL' and lower(INDEX_NAME)='idx_pad';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PAD                        VALID