如何解析分區索引中local index索引和global index

142次閱讀

共計 8839 個字符，預計需要花費 23 分鐘才能閱讀完成。

本篇文章給大家分享的是有關如何解析分區索引中 local index 索引和 global index，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

分區索引分為本地 (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 索引

以上就是如何解析分區索引中 local index 索引和 global index，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。

正文完