驗證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