Oracle分區之三：索引分割區

分區索引分為本地(local index)索引和全域索引(global index)。對於local索引，每一個表分區對應一個索引分割區，當表的分區發生變化時，索引的維護由Oracle自動進行。對於global索引，可以選擇是否分區，而且索引的分區可以不與表分區相對應。當對分區進行維護操作時，通常會導致全域索引的INVALDED，必須在執行完操作後REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES語句，可以使在進行分區維護的同時重建全域索引其中本地索引又可以分為有首碼(prefix)的索引和無首碼(nonprefix)的索引。而全域索引目前只支援有首碼的索引。B樹索引和位元影像索引都可以分區，但是HASH索引不可以被分區。位元影像索引必須是本地索引。下面就介紹本地索引以及全域索引各自的特點來說明區別；

一、本地索引特點：
1，本地索引一定是分區索引，分區鍵等同於表的分區鍵，分區數等同於表的分區說，一句話，本地索引的分區機制和表的分區機制一樣。
2，如果本地索引的索引列以分區鍵開頭，則稱為首碼局部索引。
3，如果本地索引的列不是以分區鍵開頭，或者不包含分區鍵列，則稱為非首碼索引。
4，首碼和非首碼索引都可以支援索引分割區消除，前提是查詢的條件中包含索引分割區鍵。
5，本地索引只支援分區內的唯一性，無法支援表上的唯一性，因此如果要用本地索引去給表做唯一性限制式，則約束中必須要包括分區鍵列。
6，本地分區索引是對單個分區的，每個分區索引只指向一個表分區，全域索引則不然，一個分區索引能指向n個表分區，同時，一個表分區，也可能指向n個索引分割區，對分區表中的某個分區做truncate或者move，shrink等，可能會影響到n個全域索引分割區，正因為這點，本地分區索引具有更高的可用性。
7，位元影像索引只能為本地分區索引。
8，本地索引多應用於資料倉儲環境中。
本地索引:建立了一個分區表後，如果需要在表上面建立索引，並且索引的分區機制和表的分區機制一樣，那麼這樣的索引就叫做本地分區索引。本地索引是由ORACLE自動管理的，它分為有首碼的本地索引和無首碼的本地索引。什麼叫有首碼的本地索引？有首碼的本地索引就是包含了分區鍵，並且將其作為引導列的索引。什麼叫無首碼的本地索引？無首碼的本地索引就是沒有將分區鍵的前置列作為索引的前置列的索引。下面舉例說明：

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

create or replace procedure proc1
as
begin
 for i in 1..100000
 loop
  execute immediate
  'INSERT INTO warecountd values(:x,:y)' USING i,to_char(i+1000000);
 end loop;
end;
/

SQL> exec proc1

PL/SQL procedure successfully completed.

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

SQL> select dbms_metadata.get_ddl('INDEX','I_ID','HR') index_name FROM DUAL; 

看系統的對索引的metadata
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','I_ID','HR') index_name FROM DUAL;

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

  CREATE INDEX "HR"."I_ID" ON "HR"."TEST" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
 (PARTITION "P1"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P1" ,
 PARTITION "P2"

INDEX_NAME
--------------------------------------------------------------------------------
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P2" ,
 PARTITION "P3"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P3" )

當然你也可以用上面的語句建立index，也可以用下面的SQL語句建立：

SQL> drop index i_id;

Index dropped

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

Index created

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

看系統的對索引的metadata
SQL> select dbms_metadata.get_ddl('INDEX','I_DATA','HR') index_name FROM DUAL;

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

  CREATE INDEX "HR"."I_DATA" ON "HR"."TEST" ("DATA")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
 (PARTITION "P1"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P1" ,
 PARTITION "P2"

INDEX_NAME
--------------------------------------------------------------------------------
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P2" ,
 PARTITION "P3"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "P3" )

當然你也可以用上面的語句建立index，也可以用下面的SQL語句建立：

SQL> drop index i_data;

CREATE INDEX "HR"."I_DATA" ON "HR"."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                     PARTITI LOCALI ALIGNMENT
------------------------------ ------------------------------ ------- ------ ------------
I_DATA                         TEST                           RANGE  
LOCAL  NON_PREFIXED
I_ID                           TEST                           RANGE  
LOCAL  PREFIXED

 

二、全域索引特點：
1，全域索引的分區鍵和分區數和表的分區鍵和分區數可能都不相同，表和全域索引的分區機制不一樣。
2，全域索引可以分區，也可以是不分區索引，全域索引必須是首碼索引，即全域索引的索引列必須是以索引分割區鍵作為其前幾列。
3，全域分區索引的索引條目可能指向若干個分區，因此，對於全域分區索引，即使只截斷一個分區中的資料，都需要rebulid若干個分區甚至是整個索引。
4，全域索引多應用於oltp系統中。
5，全域分區索引只按範圍或者散列hash分區，hash分區是10g以後才支援。
6，oracle9i以後對分區表做move或者truncate的時可以用update global indexes語句來同步更新全域分區索引，用消耗一定資源來換取高度的可用性。
7，表用a列作分區，索引用b做局部分區索引，若where條件中用b來查詢，那麼oracle會掃描所有的表和索引的分區，成本會比分區更高，此時可以考慮用b做全域分區索引。

 

全域索引：與本地分區索引不同的是，全域分區索引的分區機制與表的分區機制不一樣。全域分區索引全域分區索引只能是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
   partition by range(id)
   ( partition p1 values less than (10000) tablespace p1,
    partition p2 values less than (20000) tablespace p2,
    partition p3 values less than (MAXVALUE) tablespace p3
  );

Index created

 

ORACLE預設不會自動維護全域分區索引，先注意看下status列，都是USABLE的

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
I_ID_GLOBAL                    P3                             USABLE

SQL> alter table test drop partition p2; ---注意看我做了DROP P2索引STATUS還是沒有變

Table altered.

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
I_ID_GLOBAL                    P3                             USABLE

 

SQL> create index i_id_global_data on test(data) global
 partition by range(id)
( partition p1 values less than (10000) tablespace p1,
 partition p2 values less than (20000) tablespace p2,
 partition p3 values less than (MAXVALUE) tablespace p3
 );

ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed   即提示：GLOBAL  分區索引必須加上首碼

 

SQL> create bitmap index i_bmp_id_global on test(id) global
 partition by range(id)
( partition p1 values less than (10000) tablespace p1,
 partition p2 values less than (20000) tablespace p2,
 partition p3 values less than (MAXVALUE) tablespace p3
 );

ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index   ---即提示：GLOBAL 可能無法與位元影像索引一起使用

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

SQL> alter index i_id_global rebuild online nologging;
alter index i_id_global rebuild online nologging
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole  ---即不能將分區索引作為整體重建

這個時候可以通過查詢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
I_ID_GLOBAL                    P3

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.

SQL>  alter index i_id_global rebuild partition p3 online nologging;

Index altered.

四、關於分區索引的幾個視圖

dba_ind_partitions 描述了每個分區索引的分區情況，以及統計資訊
dba_part_indexes   分區索引的概要統計資訊，可以得知每個表上有哪些分區索引，分區索引的類型(local/global)