Oracle 分區表的索引、分區索引

Oracle 分區表的索引、分區索引

對于分區表，可以建立不分區索引。也就是說表分區，但是索引不分區。以下着重介紹分區表的分區索引。

索引與表一樣，也可以分區。索引分為兩類：locally partition index（局部分區索引）、globally partition index（全局分區索引）。

局部分區索引随表對索引完成相應的分區，即索引會使用與表相同的機制進行分區，每個表分區都有一個索引分區，并且隻索引該表分區。

▶ 局部字首索引（local prefixed index）：以分區鍵作為索引定義的第一列

▶ 局部非字首索引（local nonprefixed index）：分區鍵沒有作為索引定義的第一列

注意：判斷局部索引是字首還是非字首的隻需要看分區鍵是否作為索引定義的第一列

示例語句

--範圍分區

--建立表

create table student_range_part(

stu_id varchar2(4),

stu_name varchar2(100), --姓名

sex varchar2(1),  --性别 1 男  2 女  0 未知

credit integer default 0

)

partition by range (credit)

 (

 partition student_part1 values less than (60) tablespace kdhist_data,

 partition student_part2 values less than (70) tablespace kdhist_data,

 partition student_part3 values less than (80) tablespace kdhist_data,

 partition student_part4 values less than (maxvalue) tablespace kdhist_data

 );

--建立局部字首索引；分區鍵（credit）作為索引定義的第一列

create index local_prefixed_index on student_range_part (credit, stu_id) local;

--建立局部非字首索引；分區鍵未作為索引定義的第一列

create index local_nonprefixed_index on student_range_part (stu_id, credit) local;

--①

select * from student_range_part where credit = &credit and stu_id = &stu_id;

--②

select * from student_range_part where stu_id = &stu_id;

對于以上兩個查詢來說，如果查詢第一步是走索引的話，則：

局部字首索引 local_prefixed_index 隻對 ① 有用；

局部非字首索引 local_nonprefixed_index 則對 ① 和 ② 均有用；

如果你有多個類似 ① 和 ② 的查詢的話，則可以考慮建立局部非字首索引；如果平常多使用查詢 ① 的話，則可以考慮建立局部字首索引；

小結：

分區表一般使用局部索引。重點在于如何選擇分區表和局部索引類型。

索引按範圍（Range）或散列（Hash，Oracle 10g中引入）進行分區，一個分區索引（全局）可能指向任何（或全部的）表分區。

對于全局分區索引來說，索引的實際分區數可能不同于表的分區數量；

全局索引的分區機制有别于底層表，例如表可以按 credit 列劃分為10個分區，表上的一個全局索引可以按stu_id 列劃分為5個分區。

與局部索引不同，全局索引隻有一類，即全局字首索引（prefixed global index），索引分區鍵必須作為索引定義的第一列，否則執行會報錯。

--建立按age進行範圍分區的全局分區索引

create index global_index on student_range_part(credit) global

(

  partition index_part1 values less than (60),

  partition index_part2 values less than (80),

  partition index_partmax values less than (maxvalue)

);

注意：

全局索引要求最高分區（即最後一個分區）必須有一個值為 maxvalue 的最大上限值，這樣可以確定底層表的所有行都能放在這個索引中；

一般情況下，大多數分區操作（如删除一個舊分區）都會使全局索引無效，除非重建全局索引，否則無法使用

全局索引一般用于資料倉庫，許多資料倉庫系統都存在大量的資料出入，如典型的資料“滑入滑出”（即删除表中最舊的分區，并為新加載的資料增加一個新分區）。

① 去除老資料：最舊的分區要麼被删除，要麼建立一個新表，将最舊的分區資料存入，進而對舊資料進行歸檔；

② 加載新資料并建立索引：将新資料加載到一個“工作”表中，建立索引并進行驗證；

③ 關聯新資料：一旦加載并處理了新資料，資料所在的表會與分區表中的一個空分區交換，将表中的這些新加載的資料變成分區表中的一個分區（分區表會變得更大）

對于全局索引來說，這樣增删分區的過程，意味着該全局索引的失效，需重建全局索引；

在 Oracle 9i 之後，可以在分區操作期間使用 UPDATE GLOBAL INEXES 子句來維護全局索引，這意味着當在分區上執行删除、分解或其他操作時，Oracle會對原先建立的全局索引執行必要的修改，以保證它是最新的。

--删除student_range_part表中的index_part1分區，同時同步維護全局索引

alter table student_range_part drop partition index_part1 update global indexes;

使用 UPDATE GLOBAL INEXES子句後，在删除一個分區時，必須删除可能指向該分區的所有全局索引條目；

執行表與分區的交換時，必須删除指向原資料的所有全局索引條目，再插入指向剛加載的資料的新條目；

如此一來 ALTER 指令執行的工作量會大幅增加；

分區操作執行完成後重建全局索引方式占用的資料庫資源更少，是以完成的相對“更快”，但是會帶來顯著的“停機時間”（重建索引時會有一個可觀的不可用視窗）；

在分區操作執行的同時執行 UPDATE GLOBAL INEXES 子句方式會占用更多的資源，且可能需要花費更長的時間才能完成操作，但好處是不會帶來任何的停機時間。

即使是資料倉庫，除非特殊需求，否則這個建立局部索引即可。

分區索引：https://www.cnblogs.com/Dreamer-1/p/6132776.html