全域分區索引與局部分區索引

        分區索引        分區索引，有是全域分區索引與局部分區索引，加上一種全域非分區索引（也就是普通索引），加起來共三種。下面我們討論了這三種索引的組織圖以及應用情境。

1.全域非分區索引可以依賴普通的表，也可以依賴分區表建立。CREATE INDEX month_ix ON sales(sales_month);等同於CREATE INDEX month_ix ON sales(sales_month) GLOBAL;
2.全域分區索引    全域分區索引使用一種有別於底層表的分區機制，意思是索引的分區鍵可以選擇跟表的分區鍵不一致，但索引的索引鍵首碼要包含索引的分區鍵。也就是只有”全域首碼索引“，而沒有“全域非首碼索引”。這樣，拿了索引分割區鍵做首碼的索引，即使不包含表分區鍵，也能用於表的unique與primary約束。    建成後有多個段，每個段代表一個索引分割區，每個索引分割區中的鍵值可以指向任何錶分區。可以依賴普通的表，也可以依賴分區表建立。可能索引分割區數不等於表分區數。只能按range或hash(10g起)對索引分割區。全域索引的range分區最後一個分區必須是maxvalue，以保證底層表的所有行都能放到這個索引中。CREATE INDEX month_ix ON sales(sales_month,sales_date) GLOBAL PARTITION BY RANGE(sales_month)      (PARTITION pm1_ix VALUES LESS THAN (2),       PARTITION pm2_ix VALUES LESS THAN (3),       PARTITION pm3_ix VALUES LESS THAN (4),       PARTITION pm4_ix VALUES LESS THAN (5),       PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
    全域索引建立時global 子句允許指定索引的範圍值，這個範圍值是索引分割區鍵的範圍。全域分區索引的GLOBAL PARTITION BY RANGE(sales_month)的sales_month是指定索引分割區鍵，可以跟表分區鍵不一樣，我行我素地設立分區鍵，此時sales(sales_month,sales_date)句子，指定索引鍵，其首碼就必須包含索引分割區鍵了。這一切，都可以跟底層表沒啥關係。
使用情境：對於資料倉儲，例如不斷有舊資料的刪除與新資料的流入（滑動視窗），全域索引很容易失效，使效能受影響。3.局部分區索引    不能對普通表建這個索引，只能依賴分區表建立，並且是依賴分區表的分區鍵來建立，即依賴底層表的分區機制來建立索引。隨著表分區，建立一一對應的索引分割區，每個索引分割區中的條目都只指向一個表分區。CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;create index dinya_idx_t on dinya_test(item_id)local(partition idx_1 tablespace tbs1,partition idx_2 tablespace tbs2,partition idx_3 tablespace tbs3);局部分區索引邏輯上可以劃分為：局部首碼索引--表分區鍵在索引定義的第一列上。例如對錶的欄位LOAD_DATE進行range分區，而建索引時，LOAD_DATE又是索引的第一列。局部非首碼索引--索引不以表分區鍵作為它的索引欄位的第一列，甚至壓根不包含分區鍵。
局部首碼索引與局部非首碼索引，對分區消除的影響？    首先我們得明白什麼是分區消除。一個事務，可以只考慮特定的分區，其餘分區就算物理介質損壞，其他分區所在資料表空間offline等，事務都可以不理會以及不掃描他們。分區消除的種類：表的分區消除，與索引的分區消除。分區消除更多的是為了可用性，以及在出現全表掃與全索引掃的時候，轉換為只掃特定的分區以提高效能。    能否使用分區消除，關鍵在於謂詞是否有分區鍵。如果謂詞包含分區鍵，那可能是實現索引分割區消除，也可能是表分區消除。如果謂詞不包含分區鍵，那神馬分區消除都是奢想。至於使用的是局部首碼索引還是局部非首碼索引，影響的只是能否實現索引分割區消除。用局部首碼索引才能實現索引分割區消除，用局部非首碼索引，不能實現“索引分割區消除“（但表的分區消除仍然可能實現,但當cbo評估出來要先走索引，卻發現索引分割區不可用，如所在資料表空間offline了，此時已不能改路了）。cbo評估代價時，不會考慮分區索引是否可用，評估出一個路徑，走下去發現此路不通，也不能走回頭路了而直接報錯了。
局部首碼索引與局部非首碼索引，對於sql執行效能？如果將索引作為查詢計劃的第一步，效率上其實並沒有什麼區別，儘管首碼與非首碼索引會影響到是否能使用分區消除，但分區消除是什麼呢？是可用性的提高，以及將全表掃描轉為單分區全掃的效能上的最佳化。所以對於走索引作為第一步，是否分區消除不要緊，從而是否首碼也就不要緊了。
局部首碼索引與局部非首碼索引的選擇？    怎麼選擇，首先應該是能滿足需求的。你如果建立一個(b,a)的索引，卻總查where a=3，引出很多skip scan那就不好了，此時是應該換成建立(a,b)的索引。    如果僅僅有where a=1 and b=2這樣的查詢，你可能會問，我們是建(a,b)好還是(b,a)好呢，看哪個欄位的選擇性好，看我們有沒有必要走a的索引分割區消除，假如b的密度很大，從1-50000都有，而a只能是1與2，那麼我們把b排前面更好。所以將哪個欄位放前面，得滿足業務需求、綜合謂詞的分區消除，與欄位選擇率來選擇。
局部索引與唯一約束分區表欄位想用unique或primary key約束，一般是使用全域索引來保證唯一性，這是一般的做法。因為局部索引只保證分區內部的鍵的唯一性，而不能跨分區，如果你的確想用局部索引來保證整個表的唯一性，就得把分區鍵加到約束當中，也成。如果oracle允許局部索引（不包含約束的情況）就能輕易來保證全表的唯一性，那麼所有的update與insert，都得掃每一個分區，這樣可用性與可擴充性都會喪失殆盡。
4.總結三種索引的選擇？OLAP系統中多用局部索引，OLTP系統上，全域索引更為常見。可用性角度：局部索引更可用，就算一個索引分割區出問題了也不影響其他，而全域索引很可能會成為一個故障點，一旦出現問題則整個索引都不可用。維護性角度：局部索引更好維護更靈活，DBA決定移動一個表分區，只需要重建與維護一個索引分割區。對全域索引，很多情況下都需重建。sql效率：因為局部索引隨表分區，可以涉及出最優的執行計畫。
視圖select * from DBA_IND_PARTITIONS where index_name='LOCAL_NOPREFIXED';select * from DBA_PART_INDEXES where index_name='LOCAL_NOPREFIXED';select * from DBA_PART_KEY_COLUMNS where name='LOCAL_NOPREFIXED';

實驗：--建立資料表空間tbs1,tbs2,tbs3create tablespace tbs1 datafile '+DATA6_MIDG';create tablespace tbs2 datafile '+DATA6_MIDG';create tablespace tbs3 datafile '+DATA6_MIDG';SQL>--建立一個range分區表create table t_part(a int,b int,data char(20))partition by range (a)(partition p1 values less than(2) tablespace tbs1,partition p2 values less than(3) tablespace tbs2);--插入一些資料insert into t_part select mod(rownum-1,2)+1,rownum,'x' from all_objects;commit;SQL> select * from t_part where rownum<=10;--建立局部首碼索引與局部非首碼索引create index local_prefixed on t_part(a,b) local;create index local_noprefixed on t_part(b) local;create index local_prefixed on t_part(a,b) local(partition ind1 tablespace tbs3,partition ind2 tablespace tbs2);
--為表收集統計資訊begin dbms_stats.gather_table_stats(user,'t_part',cascade=>TRUE);end;--把tbs2資料表空間下線，此時可以驗證索引分割區消除，將tbs1下線，可以驗證表分區消除。alter tablespace tbs3 offline;--再用這種命令來測試select * from t_part where a=1 and b=1;select * from t_part where b=1;select /*+full(t_part)*/ * from t_part where a=1 and b=1;