如何解析分區(qū)索引中l(wèi)ocal index索引和global index

本篇文章給大家分享的是有關(guān)如何解析分區(qū)索引中l(wèi)ocal index索引和global index，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

分區(qū)索引分為本地(local index)索引和全局索引(global index)

其中本地索引又可以分為有前綴(prefix)的索引和無(wú)前綴(nonprefix)的索引。而全局索引目前只支持有前綴的索引。B樹(shù)索引和位圖索引都可以分區(qū)，但是HASH索引不可以被分區(qū)。位圖索引必須是本地索引。下面就介紹本地索引以及全局索引各自的特點(diǎn)來(lái)說(shuō)明區(qū)別；

一、本地索引特點(diǎn)：
分區(qū)索引就是在所有每個(gè)區(qū)上單獨(dú)創(chuàng)建索引，它能自動(dòng)維護(hù)，在drop或truncate某個(gè)分區(qū)時(shí)不影響該索引的其他分區(qū)索引的使用，也就是索引不會(huì)失效，維護(hù)起來(lái)比較方便，但是在查詢性能稍微有點(diǎn)影響。

      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. 本地索引一定是分區(qū)索引，分區(qū)鍵等同于表的分區(qū)鍵，分區(qū)數(shù)等同于表的分區(qū)說(shuō)，一句話，本地索引的分區(qū)機(jī)制和表的分區(qū)機(jī)制一樣。

2. 如果本地索引的索引列以分區(qū)鍵開(kāi)頭，則稱為前綴局部索引。

3. 如果本地索引的列不是以分區(qū)鍵開(kāi)頭，或者不包含分區(qū)鍵列，則稱為非前綴索引。

4. 前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。

5. 本地索引只支持分區(qū)內(nèi)的唯一性，無(wú)法支持表上的唯一性，因此如果要用本地索引去給表做唯一性約束，則約束中必須要包括分區(qū)鍵列。

6. 本地分區(qū)索引是對(duì)單個(gè)分區(qū)的，每個(gè)分區(qū)索引只指向一個(gè)表分區(qū)，全局索引則不然，一個(gè)分區(qū)索引能指向n個(gè)表分區(qū)，同時(shí)，一個(gè)表分區(qū)，也可能指向n個(gè)索引分區(qū)，對(duì)分區(qū)表中的某個(gè)分區(qū)做truncate或者move，shrink等，可能會(huì)影響到n個(gè)全局索引分區(qū)，正因?yàn)檫@點(diǎn)，本地分區(qū)索引具有更高的可用性。

7. 位圖索引只能為本地分區(qū)索引。

8. 本地索引多應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中。

本地索引:創(chuàng)建了一個(gè)分區(qū)表后，如果需要在表上面創(chuàng)建索引，并且索引的分區(qū)機(jī)制和表的分區(qū)機(jī)制一樣，那么這樣的索引就叫做本地分區(qū)索引。本地索引是由ORACLE自動(dòng)管理的，它分為有前綴的本地索引和無(wú)前綴的本地索引。什么叫有前綴的本地索引？有前綴的本地索引就是包含了分區(qū)鍵，并且將其作為引導(dǎo)列的索引。什么叫無(wú)前綴的本地索引？無(wú)前綴的本地索引就是沒(méi)有將分區(qū)鍵的前導(dǎo)列作為索引的前導(dǎo)列的索引。下面舉例說(shuō)明：

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; 因?yàn)閕d是分區(qū)鍵，所以這樣就創(chuàng)建了一個(gè)有前綴的本地索引。

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

INDEX_NAME

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

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

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

--也可以這樣創(chuàng)建：

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;因?yàn)閐ata不是分區(qū)鍵，所以這樣就創(chuàng)建了一個(gè)無(wú)前綴的本地索引。

SQL> select dbms_metadata.get_ddl('INDEX','I_DATA','ROBINSON')index_name FROM DUAL;---刪除了一些無(wú)用信息

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視圖也可以證明剛才創(chuàng)建的索引，一個(gè)是有前綴的，一個(gè)是無(wú)前綴的

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

二、全局索引特點(diǎn)：
全局索引就是在全表上創(chuàng)建索引，它可以創(chuàng)建自己的分區(qū)，可以和分區(qū)表的分區(qū)不一樣，也就是它是獨(dú)立的索引。在drop或truncate某個(gè)分區(qū)時(shí)需要?jiǎng)?chuàng)建索引alter index idx_xx rebuild，也可以alter table table_name drop partition partition_name update global indexes;實(shí)現(xiàn)，但是要花很長(zhǎng)時(shí)間在重建索引上?？梢酝ㄟ^(guò)查詢user_indexes、user_part_indexes和 user_ind_partitions視圖來(lái)查看索引是否有效

create index idx_ta_c3 on ta(c3);

或者把全局索引分成多個(gè)區(qū)(注意和分區(qū)表的分區(qū)不一樣)：

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

　　注意索引上的引導(dǎo)列要和range后列一致，否則會(huì)有ORA-14038錯(cuò)誤。

　　oracle會(huì)對(duì)主鍵自動(dòng)創(chuàng)建全局索引

　　如果想在主鍵的列上創(chuàng)建分區(qū)索引，除非主鍵包括分區(qū)鍵，還有就是主鍵建在兩個(gè)或以上列上。

　　在頻繁刪除表的分區(qū)且數(shù)據(jù)更新比較頻繁時(shí)為了維護(hù)方便避免使用全局索引。

1.全局索引的分區(qū)鍵、分區(qū)數(shù)和表的分區(qū)鍵、分區(qū)數(shù)可能都不相同，表和全局索引的分區(qū)機(jī)制不一樣,分為全局索引和全局分區(qū)索引，以下面示例為例（全局分區(qū)索引）。

2.全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即全局索引的索引列必須是以索引分區(qū)鍵作為其前導(dǎo)列。

3.全局分區(qū)索引的索引條目可能指向若干個(gè)分區(qū)，因此，對(duì)于全局分區(qū)索引，即使只截?cái)嘁粋€(gè)分區(qū)中的數(shù)據(jù)，都需要rebulid若干個(gè)分區(qū)甚至是整個(gè)索引。

4.全局索引多應(yīng)用于oltp系統(tǒng)中。

5.全局分區(qū)索引只按范圍或者散列hash分區(qū)，hash分區(qū)是10g以后才支持。

6.oracle9i以后對(duì)分區(qū)表做move或者truncate的時(shí)可以用update global indexes語(yǔ)句來(lái)同步更新全局分區(qū)索引，用消耗一定資源來(lái)?yè)Q取高度的可用性。

7.表用a列作分區(qū)，索引用b做局部分區(qū)索引，若where條件中用b來(lái)查詢，那么oracle會(huì)掃描所有的表和索引的分區(qū)，成本會(huì)比分區(qū)更高，此時(shí)可以考慮用b做全局分區(qū)索引。
全局索引：與本地分區(qū)索引不同的是，全局分區(qū)索引的分區(qū)機(jī)制與表的分區(qū)機(jī)制不一樣。全局分區(qū)索引全局分區(qū)索引只能是B樹(shù)索引，到目前為止(10gR2)，oracle只支持有前綴的全局索引。另外oracle不會(huì)自動(dòng)的維護(hù)全局分區(qū)索引，當(dāng)我們?cè)趯?duì)表的分區(qū)做修改之后，如果執(zhí)行修改的語(yǔ)句不加上update global indexes的話，那么索引將不可用。

以上面創(chuàng)建的分區(qū)表test為例，講解全局分區(qū)索引：

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默認(rèn)不會(huì)自動(dòng)維護(hù)全局分區(qū)索引，注意看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 分區(qū)索引必須加上前綴

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 可能無(wú)法與位圖索引一起使用

三、分區(qū)索引不能夠?qū)⑵渥鳛檎w重建，必須對(duì)每個(gè)分區(qū)重建

SQL> alter index i_id_global rebuild online nologging;

alter index i_id_global rebuild online nologging

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

--這個(gè)時(shí)候可以查詢dba_ind_partitions，或者user_ind_partitions，找到partition_name，然后對(duì)每個(gè)分區(qū)重建

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

四、關(guān)于分區(qū)索引的幾個(gè)視圖

dba_ind_partitions 描述了每個(gè)分區(qū)索引的分區(qū)情況，以及統(tǒng)計(jì)信息
dba_part_indexes 分區(qū)索引的概要統(tǒng)計(jì)信息，可以得知每個(gè)表上有哪些分區(qū)索引，分區(qū)索引的類(lèi)型(local/global)
dba_indexes minus dba_part_indexes （minus操作）可以得到每個(gè)表上有哪些非分區(qū)索引

五、實(shí)驗(yàn)
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.

查詢發(fā)現(xiàn)全局索引不在視圖dba_ind_partitions中。

 
--查看是否是分區(qū)索引:  
 
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不是分區(qū)索引，而T48_TRANSACTION_MODEL_IDX1是分區(qū)索引。

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

 
總結(jié):  
全局索引：  
優(yōu)點(diǎn)：通過(guò)索引檢索，沒(méi)有限定分區(qū)的謂詞、或跨分區(qū)時(shí)，性能好點(diǎn)，  
缺點(diǎn)：分區(qū)維護(hù)的時(shí)候麻煩，drop分區(qū)等維護(hù)會(huì)失效，dml的時(shí)候索引維護(hù)成本高，數(shù)據(jù)大了rebuild也難  
 
local 索引：  
優(yōu)點(diǎn)：通過(guò)索引檢索，有限定分區(qū)的謂詞、不跨分區(qū)時(shí)，性能好，分區(qū)維護(hù)容易，dml的索引維護(hù)底，rebuild也方便。  
缺點(diǎn)：通過(guò)索引檢索，又沒(méi)有限定分區(qū)的謂詞、或跨分區(qū)時(shí)，性能不如全局索引  
 
有分區(qū)裁剪的,那么其他列就建立分區(qū)索引  
 
沒(méi)有分區(qū)裁剪的,那么列就建立global 索引 

以上就是如何解析分區(qū)索引中l(wèi)ocal index索引和global index，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。