【DB筆試面試554】在Oracle中，分區索引分為哪幾類？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，分區索引分為哪幾類？

♣

答案部分

索引按照是否分區可以分為分區索引（Partitioned Indexes）和非分區索引（NonPartitioned Indexes），如下圖所示：

圖 3-12 分區索引

與分區表類似，分區索引被分解成更小、更易於管理的索引片斷。分區索引提高了可管理性、可用性、和可擴展性。分區索引根據索引列是否包含分區鍵及分區鍵是否是索引的引導列可以分為有前綴的分區索引和無前綴的分區索引。有前綴的分區索引指的是包含了分區鍵，並且將其作為引導列的索引。無前綴的分區索引的列不是以分區鍵開頭，或者不包含分區鍵列。

分區索引就是簡單地把一個索引分成多個片斷。通過把一個索引分成多個片斷，可以訪問更小的片斷（也更快），並且可以把這些片斷分別存放在不同的磁碟上，從而避免I/O問題。B-Tree和點陣圖索引都可以被分區，而HASH索引不可以被分區。可以有好幾種分區方法：表被分區而索引未被分區；表未被分區而索引被分區；表和索引都被分區。不管採用哪種方法，都必須使用基於成本的優化器。有兩種類型的分區索引：本地分區索引和全局分區索引。每個類型都有兩個子類型，有前綴索引和無前綴索引。表各列上的索引可以有各種類型索引的組合。如果使用了點陣圖索引，那麼就必須是本地索引。索引分區最主要的原因是可以減少所需讀取的索引的大小，另外把分區放在不同的表空間中可以提高分區的可用性和可靠性。在使用分區後的表和索引時，Oracle還支援並行查詢和並行DML。這樣就可以同時執行多個進程，從而加快處理SQL語句。

（一）本地分區索引（Local Partitioned Indexes）

本地分區索引也叫局部分區索引。在本地分區索引中，索引基於表上相同的列來分區，與表分區具有相同分區數目和相同的分區邊界。每個索引分區僅與底層表的一個分區相關聯，所以，一個索引分區中的所有鍵都只引用存儲在某個單一表分區中的行。通過這種方式，資料庫會自動同步索引分區及其關聯的表分區，使每個表和索引保持獨立。

本地分區索引在數據倉庫環境中很常見，它有以下優點：

l 因為使分區中的數據無效或不可用的操作只會影響當前分區，這有助於提高可用性。

l 簡化了分區維護。當移動一個表分區，或當某個分區的數據老化時，只須重建或維持相關聯的本地索引分區。而在全局索引中所有索引分區必須被全部重建或維護。

l 如果分區發生時間點恢復，那麼可以將局部索引恢復到指定的恢復時間，而不需要重建整個索引。

本地分區索引其分區形式與表的分區完全相同，依賴列相同，存儲屬性也相同。對於本地分區索引，其索引分區的維護自動進行，也就是說，當執行ADD、DROP、SPLIT或TRUNCATE的時候，本地分區索引會自動維護其索引分區。本地分區索引的分區屬性完全繼承於表的分區屬性，包括分區類型，分區的範圍值既不需指定也不能更改。對於本地索引的分區名稱，以及分區所在表空間等資訊是可以自定義的，例如以下語句創建的是本地分區索引，且每個分區對應於不同的表空間：

CREATE INDEX IDX_PART_RANGE_ID ON T_PARTITION_RANGE(ID) LOCAL (    PARTITION I_RANGE_P1 TABLESPACE TS_DATA01,    PARTITION I_RANGE_P2 TABLESPACE TS_DATA02,    PARTITION I_RANGE_P3 TABLESPACE TS_DATA03,    PARTITION I_RANGE_PMAX TABLESPACE TS_DATA04    );  

本地分區索引可分為以下類別：

n 本地前綴索引（Local Prefixed Indexes）在這種情況下，分區鍵處於索引定義的前導部分。

n 本地非前綴索引（Local Nonprefixed Indexes）在這種情況下，分區鍵不是索引列列表的前導部分，甚至根本不必在該列表中。

這兩種類型的索引都可以充分利用分區消除（也稱為分區剪除），此時，優化程式將不予考慮無關分區，以加快數據訪問速度。查詢是否可以消除分區取決於查詢謂詞。使用本地前綴索引的查詢始終允許索引分區消除，而使用一個本地非前綴索引的查詢則可能不會利用到分區消除。

（二）全局分區索引

全局索引（Global Index）既可以分區（全局分區索引），也可以不分區（普通索引），既可以建RANGE分區，也可以建HASH分區，既可創建於分區表上，也可以創建於非分區表上，就是說，全局索引是完全獨立的，因此，它也需要更多的維護操作。

全局分區索引是一個B-Tree索引，其分區獨立於所依賴的基礎表。某個索引分區可以指向任意或所有的表分區，而在一個局部分區索引中，索引分區與分區表之間卻存在一對一的配對關係。全局分區索引是通過指定GLOBAL參數指定的。本地分區索引比全局分區索引更容易管理，但是全局索引比較快。本地索引肯定是分區索引，但是全局索引可以選擇是否分區。如果分區，那麼只能是有前綴的分區索引，Oracle不支援無前綴的全局分區索引。

另外，如果對分區進行維護操作時不加上UPDATE GLOBAL INDEXES的話，那麼會導致全局索引變為無效狀態，所以，必須在執行完維護操作後重建全局索引。

關於全局索引，需要注意以下幾點內容：

①　全局索引可以是分區索引，也可以是不分區的索引，全局索引必須是前綴索引，即全局索引的索引列必須是以索引分區鍵作為其前導列。

②　全局索引可以依附於分區表，也可以依附於非分區表。

③　全局分區索引的索引條目可能指向若干個分區，因此，對於全局分區索引，即使只截斷一個分區中的數據，也需要REBULID若干個分區甚至是整個索引。

④　全局索引多應用於OLTP系統中。

⑤　全局分區索引只按RANGE或者HASH分區，HASH分區是Oracle 10g以後才支援的。

⑥　在Oracle 9i以後對分區表做MOVE或者TRUNCATE的時候可以用UPDATE GLOBAL INDEXES語句來同步更新全局分區索引，用消耗一定資源來換取高度的可用性。

⑦　若在表中使用A列作分區，但在索引中用B列作本地索引，若WHERE條件中用B來查詢，那麼Oracle會掃描所有的表和索引的分區，成本會比分區更高，此時可以考慮用B列做全局分區索引和用A列做本地索引。

⑧　在創建索引時，如果不顯式指定GLOBAL或LOCAL，那麼默認是GLOBAL。

⑨　在創建GLOBAL索引時，如果不顯式指定分區子句，那麼默認不分區。

⑩　含有子分區的分區索引有大小，但是在數據字典視圖中的列SEGMENT_CREATED的值顯示為N/A，STATUS的值也顯示為N/A。

有關分區索引的一些數據字典視圖如下所示：

l DBA_PART_INDEXES 分區索引的概要統計資訊，可以得知每個表上有哪些分區索引，分區索引的類型是LOCAL還是GLOBAL。

l DBA_IND_PARTITIONS 每個分區索引的分區統計資訊。

l DBA_INDEXES和DBA_PART_INDEXES 可以得到每個表上有哪些非分區索引。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。