分區索引小結

分區索引分為全域索引和本地索引

其中本地索引又可以分為有首碼(prefix)的索引和無首碼(nonprefix)的索引。而全域索引目前只支援有首碼的索引。B樹索引和位元影像索引都可以分區，但是HASH索引不可以被分區。位元影像索引必須是本地索引。

一般使用LOCAL索引較為方便，而且維護代價較低，並且LOCAL索引是在分區的基礎上去建立索引，類似於在一個子表內部去建立索引，這樣開銷主要是區分分區上，很規範的管理起來，在OLAP系統中應用很廣泛；而相對的GLOBAL索引是全域類型的索引，根據實際情況可以調整分區的類別，而並非按照分區結構一一定義，相對維護代價較高一些，在OLTP環境用得相對較多  

一：本地索引:建立了一個分區表後，如果需要在表上面建立索引，並且索引的分區機制和表的分區機制一樣，那麼這樣的索引就叫做本地分區索引。本地索引是由ORACLE自動管理的，它分為有首碼的本地索引和無首碼的本地索引。什麼叫有首碼的本地索引？有首碼的本地索引就是包含了分區鍵，並且將其作為引導列的索引。什麼叫無首碼的本地索引？無首碼的本地索引就是沒有將分區鍵的前置列作為索引的前置列的索引。下面舉例說明：

create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (1000) tablespace p1,
partition p2 values less than (2000) tablespace p2,
partition p3 values less than (maxvalue) tablespace p3
);

create index i_id on test(id) local; 因為id是分區鍵，所以這樣就建立了一個有首碼的本地索引。

SQL> select dbms_metadata.get_ddl('INDEX','I_ID','ROBINSON') index_name FROM DUAL;  ------去掉了一些無用資訊

INDEX_NAME

 

--------------------------------------------------------------------------------

 CREATE INDEX "ROBINSON"."I_ID" ON "ROBINSON"."TEST" ("ID") LOCAL

 (PARTITION "P1" TABLESPACE "P1" , PARTITION "P2" TABLESPACE "P2" ,PARTITION "P3" TABLESPACE "P3" );

也可以這樣建立：

SQL> drop index i_id;

Index dropped

SQL> CREATE INDEX "ROBINSON"."I_ID" ON "ROBINSON"."TEST" ("ID") LOCAL
  2  (PARTITION "P1" TABLESPACE "P1" , PARTITION "P2" TABLESPACE "P2" ,PARTITION "P3" TABLESPACE "P3" );

Index created

create index i_data on test(data) local;因為data不是分區鍵，所以這樣就建立了一個無首碼的本地索引。

SQL> select dbms_metadata.get_ddl('INDEX','I_DATA','ROBINSON') index_name FROM DUAL; ---我刪除了一些無用資訊

INDEX_NAME
--------------------------------------------------------------------------------

 CREATE INDEX "ROBINSON"."I_DATA" ON "ROBINSON"."TEST" ("DATA") LOCAL
 (PARTITION "P1"  TABLESPACE "P1" ,PARTITION "P2"  TABLESPACE "P2" , PARTITION "P3" TABLESPACE "P3" )；

從user_part_indexes視圖也可以證明剛才建立的索引，一個是有首碼的，一個是無首碼的

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes;

INDEX_NAME                     TABLE_NAME                     PARTITIONING_TYPE LOCALITY ALIGNMENT
------------------------------ ------------------------------ ----------------- -------- ------------
I_DATA                         TEST                           RANGE             LOCAL    NON_PREFIXED
I_ID                           TEST                           RANGE             LOCAL    PREFIXED

二：全域索引：與本地分區索引不同的是，全域分區索引的分區機制與表的分區機制不一樣。全域分區索引全域分區索引只能是B樹索引，到目前為止(10gR2)，oracle只支援有首碼的全域索引。另外oracle不會自動的維護全域分區索引，當我們在對錶的分區做修改之後，如果執行修改的語句不加上update global indexes的話，那麼索引將不可用。以剛才建立的分區表test為例，講解全域分區索引

SQL> drop index i_id ;

Index dropped

SQL> create index i_id_global on test(id) global
  2  partition by range(id)
  3  ( partition p1 values less than (2000) tablespace p1,
  4    partition p2 values less than (maxvalue) tablespace p2
  5  );

Index created

SQL> alter table test drop partition p3;

Table altered

ORACLE預設不會自動維護全域分區索引，注意看status列，

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where index_name='I_ID_GLOBAL';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
I_ID_GLOBAL                    P1                             USABLE
I_ID_GLOBAL                    P2                             USABLE

SQL> create index i_id_global on test(data) global
  2  partition by range(id)
  3  ( partition p1 values less than (2000) tablespace p1,
  4    partition p2 values less than (maxvalue) tablespace p2
  5  );

create index i_id_global on test(data) global
partition by range(id)
( partition p1 values less than (2000) tablespace p1,
  partition p2 values less than (maxvalue) tablespace p2
)

ORA-14038: GLOBAL  分區索引必須加上首碼

SQL> create bitmap index i_id_global on test(id) global
  2  partition by range(id)
  3  ( partition p1 values less than (2000) tablespace p1,
  4    partition p2 values less than (maxvalue) tablespace p2
  5  );

create bitmap index i_id_global on test(id) global
partition by range(id)
( partition p1 values less than (2000) tablespace p1,
  partition p2 values less than (maxvalue) tablespace p2
)

ORA-25113: GLOBAL 可能無法與位元影像索引一起使用

三：分區索引不能夠將其作為整體重建，必須對每個分區重建

SQL> alter index i_id_global rebuild online nologging;

alter index i_id_global rebuild online nologging

ORA-14086: 不能將分區索引作為整體重建

這個時候可以查詢dba_ind_partitions，或者user_ind_partitions，找到partition_name，然後對每個分區重建

SQL> select index_name,partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
I_ID_GLOBAL                    P1
I_ID_GLOBAL                    P2

SQL> alter index i_id_global rebuild partition p1 online nologging;

Index altered

SQL> alter index i_id_global rebuild partition p2 online nologging;

Index altered

四、建立分區索引與普通索引的區別

  1、建立普通的索引  

  create index no_part_idx on p_list(sale_date);   

  2、建立本地分區索引  

create index local_part_idx on p_list(sale_date) local;   

  3、建立全域分區索引  

create index glo_part_idx on p_list(sale_date) global partition by range(sale_date) ( 

PARTITION p1 VALUES less than ('20121111')   TABLESPACE system,   

PARTITION p2 VALUES less than ('20121112') TABLESPACE system, 

PARTITION pmax VALUES less than (maxvalue) TABLESPACE system );    

四、刪除索引與索引失效：   

對於分區索引的刪除，local index 不能指定分區名稱，需單獨的刪除分區索引。  local index 對應的分區會伴隨著data分區的刪除而一起被刪除。globalpartition index 可以指定分區名稱，刪除某一分區。但是有一點要注意，如果該分區不為空白，則會導致更高一級的索引分割區被置為UNUSABLE 。  

 ALTER INDEX gidx_range_exampel_id drop partition part_01  此句將導致part_02 狀態為UNUSABLE   

本地分區索引的狀態改變  

--整個索引不可用  

ALTER INDEX index_tmp_partition_list  UNUSABLE;  

--單個分區索引不可用  

ALTER INDEX index_tmp_partition_list MODIFY PARTITION p_20110816 USABLE;  

--分區不可用之後，需要重建索引  

alter index index_tmp_partition_list rebuild partition p_20110816;  

--查看分區索引的狀態  

select index_name,partition_name,tablespace_name,status  

fromuser_ind_partitions  wherelower(index_name)='index_tmp_partition_list'        

參考自：http://blog.csdn.net/robinson1988/article/details/5276924