關於分區技術的索引 index，分區索引index

關於分區技術的索引 index，分區索引index

分區系列篇：

關於oracle分區技術--初瞭解  http://blog.csdn.net/wanghui5767260/article/details/39158873

關於分區技術的索引：           http://blog.csdn.net/wanghui5767260/article/details/39181027

關於分區技術---索引 Index

 

一、   分區索引分類：

    本地首碼分區索引(local prefixedpartitioned index)

    全域分區索引(global partitionedindex)

    本地非首碼分區索引(localnon-prefixed partitioned index)

 

1.1  表和索引的組合：

 

第一種:表和索引都不分區 

    最簡單的方式  就是常見的索引

 

第二種：表分區了，但索引沒分區

    這導致了：“我們已經做了分區表了，怎麼效能沒有提高?”的主要原因之一。

在很多系統中，特別是交易系統裡面，是通過索引訪問資料庫的。如果索引沒有分區，索引樹的高度沒有變，因此訪問效能當然沒有提高。如果按索引訪問表，與表是否分區關係不大。

 

第三種：表沒有分區，但索引分割區了

    索引只能是全域分區索引。

 

第四種：表分區了，索引也分區了(重點！！！)

    全域分區索引

    本地首碼分區索引

 

二、   具體介紹分區索引

 

本地分區索引

    是指索引的分區方法與對應表的分區方法一樣。

 

2.1本地首碼分區索引（local prefixed partitioned index）

是指分區欄位是索引欄位的首碼。

    一張交易流水表(TXN_CURRENT)，並且以交易日期欄位(TXN_DATE)按年度進行了定界分割。在TXN_DATE 欄位建立索引。

 

Createindex idx_txn_current_1 on txn_current(txn_date) local;

 

或者複合分區索引

Createindex idx_txn_current_2 on txn_current(txn_date,area) local;

 

    好處：

a. 由於分區索引和表分區對應，因此查詢資料直接到對應的索引分割區去查詢，索引樹的高度肯定低於非分區情況下的那棵大索引樹了，也就是說效能更高了。

b. 當某個分區進行刪除（DROP）或合并（MERGE）操作之後，oracle自動對所對應的索引分割區進行相同的操作，整個本地首碼索引依然有效，不需要重建(rebuild)操作，這樣大大保障了表的可用性。

 

2.2 全域分區索引(global partitioned index)

    索引的分區與表分區無關

 

    分區表按年份進行分區，在地區(AREA)欄位上建立分區索引，

 

 

 

也就是說：杭州交易資料肯定可分布在各年份，湖州、嘉興交易資料同樣如此。

假如有這樣的查詢需求：

Select* from TXN_CURRENT where area=’05711001’;

--假設查詢杭州西湖區

 

好處：

a. 此時oracle會很聰明地知道杭州的分區索引樹上去檢索，索引高度肯定低於非分區情況下的大索引樹了，也就是效能更高了。

b. 在分區粒度比較細的情況下，效能甚至高於本地首碼分區索引。

 

不足：

a.  主要體現在高可用方面。如果該表的03年資料通過分區刪除全部刪除了。則全域分區索引(包括普通非分區索引)則全部失效(INVALID)，這些索引不可用了，除非重建(rebuild)操作。資料量越大，索引量也越大，重建索引時間也越長，無法通過該類索引訪問資料的時間也越長。因此，大大降低資料的可訪問性。

 

2.3 本地非首碼分區索引

可能導致效能會下降的情況，本地非首碼分區索引的效能可能還不如不分區。

 

優點：

a. 提高按索引訪問的可用性！我們假設要通過刪除分區技術，進行03年資料的清理，如果area欄位索引建立成普通索引，或者是全域分區索引，都會面臨一個問題：在分區刪除(DROP)操作之後，普通索引和全域分區索引都會失效(INVALID)，必須重建。而本地非首碼分區索引的好處在於，在分區刪除操作後，該本地非首碼分區索引依然有效。

 

三、   整理思路

 

理解分區索引藏寶圖：

 

 

描述藏寶圖：找寶藏

(1)如果表分區欄位正好是索引欄位或者是其首碼。例如：上述TXN_CURRENT 表分區欄位是TXN_DATE，則TXN_DATE正好是索引欄位（TXN_DATE），或者正好是索引欄位（TXN_DATE,AREA）的首碼，則此時應建立local profixedpartitioned index.

(2)否則，如果欲將非分區欄位建立為唯一索引，例如：假設在TXN_CURRENT 表的某個欄位建立唯一索引，oracle要求必須global prefixed index.

 否則報錯：

ORA-14039:partitioning columns must form a subset of key columns of a UNIQUE index

 

(3)流程圖再往下，此時要判斷“是否效能在可承受範圍，而分區的管理性、可用性更重要?”。如果是，就應建立local non-profixed index。也就是上面所描述的：如果曆史資料的整理非常頻繁，而且不能承受全域分區索引重建的長時間帶來的索引不可用，同時日常交易效能尚能接受，則建議設計為本地非首碼分區索引。

(4)流程圖再往下，最後判斷系統是否為交易系統或者是資料倉儲系統。因為通常情況下，資料倉儲會有頻繁的大批量資料匯入（ETL）操作，以及曆史資料清理操作，此時分區索引可用性更重要，因此建議設計為Localnon-profixed index 。而在交易系統中，日常查詢效能要求更高，曆史資料清理操作頻度相對較低，因此建議設計為global profixed index。

 註：oracle沒有global non-profixed index概念。

 

四、   分區表設計建議  只是建議：更多看實際！！！

 

(1)       表的大小：當表的大小超過1.5G—2GB時，或對於OLTP系統，表的記錄超過1000萬條時，都應考慮對錶進行分區。

(2)      資料訪問特徵：基於表的大部分查詢應用，只訪問表中的少量資料。對於這樣的表進行分區，可充分利用分區技術排除無關資料查詢的特徵。

(3)      資料維護：按時間段刪除成批的資料，例如按月刪除曆史資料。對於這樣的表需要考慮進行分區，以滿足維護的需求。

(4)      資料備份和恢複：按時間周期進行資料表空間的備份時，在分區與資料表空間之間建立起對應關係。

(5)      唯讀資料：如果一個表中的大部分資料都是唯讀資料，通過對錶進行分區，可將唯讀資料存放區在唯讀資料表空間中，對於資料庫的備是非常有益的。

(6)      並行資料操作：對於經常執行並行操作（如parallelinsert、parallel update等）的表應考慮進行分區。

(7)      表的可用性：當對錶中部分資料的可用性要求很高時，應考慮進行表分區。

2014/9/10     tyger

本文整理自：羅敏《品悟效能最佳化》

Oracle索引技術之怎建立最佳索引

怎樣建立最佳索引?1、明確地建立索引create index index_name on table_name(field_name)tablespace tablespace_namepctfree 5initrans 2maxtrans 255storage(minextents 1maxextents 16382pctincrease 0); 2、建立基於函數的索引常用與UPPER、LOWER、TO_CHAR(date)等函數分類上，例：create index idx_func on emp(UPPER(ename)) tablespace tablespace_name; 3、建立位元影像索引對基數較小，且基數相對穩定的列建立索引時，首先應該考慮位元影像索引，例：create bitmap index idx_bitm on class (classno) tablespace tablespace_name; 4、明確地建立唯一索引可以用create unique index語句來建立唯一索引，例：create unique index dept_unique_idx on dept(dept_no) tablespace idx_1; 5、建立與約束相關的索引可以用using index字句，為與unique和primary key約束相關的索引，例：alter table table_nameadd constraint PK_primary_keyname primary key(field_name)using index tablespace tablespace_name; 如何建立局部區索引？1)基礎資料表必須是分區表2)分區數量與基礎資料表相同3)每個索引分割區的子分區數量與相應的基礎資料表分區相同4)基礎資料表的自分區中的行的索引項目，被儲存在該索引的相應的自分區中，例如create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID)Pctfree 5Tablespace TBS_AK01_IDXStorage(MaxExtents 32768PctIncrease 0FreeLists 1FreeList Groups 1)local/ 如何建立定界分割的全域索引？基礎資料表可以是全域表和分區表create index idx_start_date on tg_cdr01(start_date)global partition by range(start_date)(partition p01_idx vlaues less than ('0106')partition p01_idx vlaues less than ('0111')...partition p01_idx vlaues less than ('0401'))/ 如何重建現存的索引？重建現存的索引的當前時刻不會影響查詢重建索引可以刪除額外的資料區塊提高索引查詢效率alter index idx_name rebuild nologging;對於分區索引alter index idx_name rebuild partition partition_name nologging; 刪除索引的原因？1)不再需要的索引2)索引沒有針對其相關的表所發布的查詢提供所期望的效能改善3)應用沒有用該索引來查詢資料4)該索引無效，必須在重建之前刪除該索引5)該索引已經變的太碎了，必須在重......餘下全文>>
 
oracle中為何重建分區索引（索引分割區）？ 重建分區索引與索引分割區什不同？

1.上述的語句是對單個分區索引進行重建。
2.索引分割區的意思是指建立一個分區索引，而不是一個普通索引。
3.重建分區索引，這個比如說片段太多，想換個資料表空間都可以重建的。
4.執行完了，就表示執行成功了。索引的分區資訊可以在 user_ind_partitions 查詢