分割槽索引之本地(local index)索引和全域性索引(global index)

https://www.cnblogs.com/wbzhao/archive/2012/04/01/2428219.html

分割槽索引分為本地(local index)索引和全域性索引(global index)

其中本地索引又可以分為有字首(prefix)的索引和無字首(nonprefix)的索引。而全域性索引目前只支援有字首的索引。B樹索引和點陣圖索引都可以分割槽，但是HASH索引不可以被分割槽。點陣圖索引必須是本地索引。下面就介紹本地索引以及全域性索引各自的特點來說明區別；

一、本地索引特點：
分割槽索引就是在所有每個區上單獨建立索引，它能自動維護，在drop或truncate某個分割槽時不影響該索引的其他分割槽索引的使用，也就是索引不會失效，維護起來比較方便，但是在查詢效能稍微有點影響。

      create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4); 或者 create index idx_ta_c2 on ta(c2) local ;

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 (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 LOCALNON_PREFIXED
I_ID TEST RANGE LOCALPREFIXED

 

二、全域性索引特點：
全域性索引就是在全表上建立索引，它可以建立自己的分割槽，可以和分割槽表的分割槽不一樣，也就是它是獨立的索引。在drop或truncate某個分割槽時需要建立索引alter index idx_xx rebuild，也可以alter table table_name drop partition partition_name update global indexes;實現，但是要花很長時間在重建索引上。可以透過查詢user_indexes、user_part_indexes和 user_ind_partitions檢視來檢視索引是否有效

create index idx_ta_c3 on ta(c3);

或者把全域性索引分成多個區(注意和分割槽表的分割槽不一樣)：

create index idx_ta_c4 on ta(c4) global partition by range(c4)(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue));

　　注意索引上的引導列要和range後列一致，否則會有ORA-14038錯誤。

　　oracle會對主鍵自動建立全域性索引

　　如果想在主鍵的列上建立分割槽索引，除非主鍵包括分割槽鍵，還有就是主鍵建在兩個或以上列上。

　　在頻繁刪除表的分割槽且資料更新比較頻繁時為了維護方便避免使用全域性索引。

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

四、關於分割槽索引的幾個檢視

dba_ind_partitions 描述了每個分割槽索引的分割槽情況，以及統計資訊
dba_part_indexes 分割槽索引的概要統計資訊，可以得知每個表上有哪些分割槽索引，分割槽索引的型別(local/global)
dba_indexes minus dba_part_indexes （minus操作）可以得到每個表上有哪些非分割槽索引

五、實驗
SQL> create table T48_TRANSACTION_MODEL  
     (  
       trandate    DATE,  
       orgid       VARCHAR2(11),  
       stan        NUMBER,  
       subjectno   VARCHAR2(10),  
       subjectname VARCHAR2(50),  
       acctid      NUMBER  
     )  
     partition by range (TRANDATE)  
     (  
       partition XYZ_20100000 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110101 values less than (TO_DATE(' 2011-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110102 values less than (TO_DATE(' 2011-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110103 values less than (TO_DATE(' 2011-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110104 values less than (TO_DATE(' 2011-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110105 values less than (TO_DATE(' 2011-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110106 values less than (TO_DATE(' 2011-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110107 values less than (TO_DATE(' 2011-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))  
     );

Table created.


SQL> create index T48_TRANSACTION_MODEL_IDX1 on T48_TRANSACTION_MODEL(stan) local;     

Index created.

SQL> create index T48_TRANSACTION_MODEL_IDX2 on T48_TRANSACTION_MODEL(acctid)  ;    

Index created.

SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20100000
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110101
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110102
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110103
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110104
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110105
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110106
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110107

8 rows selected.

查詢發現全域性索引不在檢視dba_ind_partitions中。

 
--檢視是否是分割槽索引:  
 
SQL> select owner,index_name,index_type,table_owner,table_name,table_type,partitioned from dba_indexes where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  PAR
------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---
SYS                            T48_TRANSACTION_MODEL_IDX2     NORMAL                      SYS                            T48_TRANSACTION_MODEL          TABLE       NO
SYS                            T48_TRANSACTION_MODEL_IDX1     NORMAL                      SYS                            T48_TRANSACTION_MODEL          TABLE       YES

查詢得索引T48_TRANSACTION_MODEL_IDX2不是分割槽索引，而T48_TRANSACTION_MODEL_IDX1是分割槽索引。

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

INDEX_NAME                     TABLE_NAME                     PARTITION LOCALI ALIGNMENT
------------------------------ ------------------------------ --------- ------ ------------
T48_TRANSACTION_MODEL_IDX1     T48_TRANSACTION_MODEL          RANGE     LOCAL  NON_PREFIXED

 
總結:  
全域性索引：  
優點：透過索引檢索，沒有限定分割槽的謂詞、或跨分割槽時，效能好點，  
缺點：分割槽維護的時候麻煩，drop分割槽等維護會失效，dml的時候索引維護成本高，資料大了rebuild也難  
 
local 索引：  
優點：透過索引檢索，有限定分割槽的謂詞、不跨分割槽時，效能好，分割槽維護容易，dml的索引維護底，rebuild也方便。  
缺點：透過索引檢索，又沒有限定分割槽的謂詞、或跨分割槽時，效能不如全域性索引  
 
有分割槽裁剪的,那麼其他列就建立分割槽索引  
 
沒有分割槽裁剪的,那麼列就建立global 索引