分割槽表 全域性索引與本地索引失效測試

  作為運維DBA，維護失效的分割槽索引是日常工作裡時有發生的事情。那麼，究其原因到底什麼操作會導致分割槽表上的索引失效呢？今天我們就來做做的分析。 從原理上來說，當資料沒有發生位置上的實質變化，既某一行資料的rowid不變，索引掃描依然可以透過索引中存放的rowid定位到這行資料時，索引不會 失效。這樣的操作有新增和刪除沒有資料的partition（太明顯了，不討論）、split沒有資料的partition。我們透過實驗來看看吧！

1、搭測試環境

--檢視資料檔案路徑

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/space/oradata/tx9ab/datafile/system01.dbf
/space/oradata/tx9ab/datafile/sysaux01.dbf
/space/oradata/tx9ab/datafile/undotbs01.dbf
/space/oradata/tx9ab/datafile/users01.dbf
/space/oradata/tx9ab/datafile/example01.dbf

--建立實驗所用的分割槽表空間

SQL>
create tablespace test001 datafile '/space/oradata/tx9ab/datafile/test001.dbf' size 10m autoextend on;
create tablespace test002 datafile '/space/oradata/tx9ab/datafile/test002.dbf' size 10m autoextend on;
create tablespace test003 datafile '/space/oradata/tx9ab/datafile/test003.dbf' size 10m autoextend on;
create tablespace test004 datafile '/space/oradata/tx9ab/datafile/test004.dbf' size 10m autoextend on;
create tablespace test005 datafile '/space/oradata/tx9ab/datafile/test005.dbf' size 10m autoextend on;
create tablespace test006 datafile '/space/oradata/tx9ab/datafile/test006.dbf' size 10m autoextend on;

Tablespace created.

SQL>

Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL> select TS#,NAME from v$tablespace;

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         4 USERS
         3 TEMP
         6 EXAMPLE
         7 TEST001
         8 TEST002
         9 TEST003
        10 TEST004
        11 TEST005

       TS# NAME
---------- ------------------------------
        12 TEST006

12 rows selected.

--建立測試表

SQL> create table pt partition by range(object_id)
(
partition p1 values less than (2000) tablespace test001,
partition p2 values less than (8000) tablespace test002,
partition p3 values less than (20000) tablespace test003,
partition p4 values less than (40000) tablespace test004,
partition pmax values less than (maxvalue) tablespace test005) as select * from dba_objects;

Table created.

--為測試表建立主鍵約束和本地索引

SQL> alter table pt add constraint pk_id primary key(object_id);

Table altered.

SQL>create index idx_pt on pt(object_name) local;

Index created.

2、檢視當前索引狀態

SQL> select index_name, status, last_analyzed, partitioned
  from dba_indexes where index_name = 'PK_ID';  

INDEX_NAME                     STATUS   LAST_ANALYZED                 PAR
------------------------------ -------- ----------------------------- ---
PK_ID                          VALID    10-AUG-2015 22:45:52                 NO

SQL> set linesize 200
SQL> col index_name for a25

select a.index_name, a.partition_name, a.tablespace_name, a.status
  from dba_ind_partitions a where a.index_name = 'IDX_PT';

INDEX_NAME                PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------- ------------------------------ ------------------------------ --------
IDX_PT           <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>