ORACLE分區表、分區索引

ORACLE分區表、分區索引ORACLE對於分區表方式其實就是將表分段儲存，一般普通表格是一個段儲存，而分區表會分成多個段，所以尋找資料過程都是先定位根據查詢條件定位分區範圍，即資料在那個分區或那幾個內部，然後在分區內部去尋找資料，一個分區一般保證四十多萬條資料就比較正常了，但是分區表並非亂建立，而其維護性也相對較為複雜一點，而索引的建立也是有點講究的，這些以下盡量闡述詳細即可。

1、類型說明：
range分區方式，也算是最常用的分區方式，其通過某欄位或幾個欄位的組合的值，從小到大，按照指定的範圍說明進行分區，我們在INSERT資料的時候就會儲存到指定的分區中。
List分區方式，一般是在range基礎上做的二級分區較多，是一種列舉方式進行分區，一般講某些地區、狀態或指定規則的編碼等進行劃分。
Hash分區方式，它沒有固定的規則，由ORACLE管理，只需要將值INSERT進去，ORACLE會自動去根據一套HASH演算法去劃分分區，只需要告訴ORACLE要分幾個區即可。

分區可以進行兩兩組合，ORACLE 11G以前兩兩組合都必須以range作為一級分區的開頭，ORACLE目前最多支援2層級分區，但這個層級已經夠我們使用了。
我這隻以最簡單的分區方式建立分區來說明問題，就拿range分區來說明問題吧（基本建立語句如下）：
CREATE [url=]TABLE[/url] TABLE_PARTITION(
       COL1   NUMBER,
       COL2   VARCHAR2(10)
) 
partition by range(COL1)(
          partition TAB_PARTOTION_01 values lessthan (450000),
          partition TAB_PARTOTION_02 values lessthan (900000),
          partition TAB_PARTOTION_03 values lessthan (1350000),
          partition TAB_PARTOTION_04 values lessthan (1800000),
          partition TAB_PARTOTION_OTHER valuesless THAN (MAXVALUE)
);

這個分區表建立了四個定長分區，理想情況下，儲存450000條資料，擴充分區是超過這個數額的分區，當發現擴充分區有資料的時候，可以進行將擴充分區做SPLIT操作，這個後面說明，這裡先說一下一些常用的分區表查詢功能，我們先插入一些資料進去。
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1,'資料測試');
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(23,'資料測試');
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(449000,'資料測試');

INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(450000,'資料測試');



INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1350000,'資料測試');

INSERT INTO TABLE_PARTITION(COL1,COL2)) 
VALUES(900000,'資料測試'); 

INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1800000-1,'資料測試');
COMMIT;
為了檢測哪些分區中有哪些資料分別按照分區去查詢資料（應用開發中基本不會用到，因為不會把分區寫死）
SQL> SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01);
      COL1     COL2
---------- ---------------
         1        資料測試
        23       資料測試
    449000    資料測試
說明第一個分區有：1、23、44900這些資料，也就是插入時，ORACLE是自己去找分區的，其實分區這種子表[url=]管理[/url]自己也可以通過程式去完成，ORACLE給你提供了一套，就可以自己去完成了。其餘的資料就自己查了，都是一個道理。

2、分區應用：
一般一張表超過2G的大小，ORACLE是推薦使用分區表的，分區一般都需要建立索引，說到分區索引，就可以分為：全域索引、分區索引，即：global索引和local索引，前者為預設情況下在分區表上建立索引時的索引方式，並不對索引進行分區（索引也是表結構，索引大了也需要分區，關於索引以後專門寫點）而全域索引可修飾為分區索引，但是和local索引有所區別，前者的分區方式完全按照自訂方式去建立，和表結構完全無關，所以對於分區表的全域索引有以下兩幅網上常用的圖解：
2.1、對於分區表的不分區索引（這個有點繞，不過就是表分區，但其索引不分區）：

建立文法(直接建立即可)：
CREATE INDEX <index_name> ON<partition_table_name>(<column_name>);



2.2、對於分區表的分區索引:

建立文法為：
CREATE [url=]INDEX[/url] INX_TAB_PARTITION_COL1 ONTABLE_PARTITION(COL1)
  GLOBAL PARTITION BY RANGE(COL1)
         PARTITION IDX_P1 values less than(1000000),
         PARTITION IDX_P2 values less than(2000000),
         PARTITION IDX_P3 values less than(MAXVALUE)
  )

2.3、LOCAL索引結構：


建立文法為：
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
也可按照分區表的的分區結構給與一一定義，索引的分區將得到重新命名。
分區上的位元影像索引只能為LOCAL索引，不能為GLOBAL全域索引。

2.4、對比索引方式：

  一般使用LOCAL索引較為方便，而且維護代價較低，並且LOCAL索引是在分區的基礎上去建立索引，類似於在一個子表內部去建立索引，這樣開銷主要是區分分區上，很規範的管理起來，在OLAP系統中應用很廣泛；而相對的GLOBAL索引是全域類型的索引，根據實際情況可以調整分區的類別，而並非按照分區結構一一定義，相對維護代價較高一些，在OLTP環境用得相對較多，這裡所謂OLTP和OLAP也是相對的，不是特殊的項目，沒有絕對的劃分概念，在應用過程中依據實際情況而定，來提高整體的運行效能。

3、常用視圖：
1、查詢目前使用者下有哪些是分區表：
SELECT * FROM USER_PART_TABLES;

2、查詢目前使用者下有哪些分區索引：
SELECT * FROM USER_PART_INDEXES;


3、查詢目前使用者下分區索引的分區資訊：
SELECT * FROM USER_IND_PARTITIONS T
WHERE T.INDEX_NAME=?

4、查詢目前使用者下分區表的分區資訊：
SELECT * FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME=?;

5、查詢某分區下的資料量：
SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);


6、查詢索引、表上在那些列上建立了分區：
SELECT * FROM USER_PART_KEY_COLUMNS;


7、查詢某使用者下二級分區的資訊（只有建立了二級分區才有資料）：
SELECT * FROM USER_TAB_SUBPARTITIONS;

4、維護操作：
4.1、刪除分區
    ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;
     如果是全域索引，因為全域索引的分區結構和表可以不一致，若不一致的情況下，會導致整個全域索引失效，在刪除分區的時候，語句修改為：
     ALTER TABLE TABLE_PARTITION DROP PARTITIONTAB_PARTOTION_03 UPDATE GLOBAL INDEXES;


4.2、分區合并（從中間刪除掉一個分區，或者兩個分區需要合并後減少分區數量）
    合并分區和刪除中間的RANGE有點像，但是合并分區是不會刪除資料的，對於LIST、HASH分區也是和RANGE分區不一樣的，其文法為：
ALTER TABLE TABLE_PARTITION MERGE PARTITIONS   TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;


4.3、分隔分區（一般分區從擴充分區從分隔）
ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)
INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);

4.4、建立新的分區（分區資料若不能提供範圍，則插入時會報錯，需要增加分區來擴大範圍）
一般有擴充分區的是都是用分隔的方式，若上述建立表時沒有建立TAB_PARTOTION_OTHER分區時，在插入資料較大時（按照上述建立規則，超過1800000就應該建立新的分區來儲存），就可以建立新的分區，如：
為了實驗，我們將擴充分區先刪除掉再建立新的分區（因為ORACLE要求，分區的資料不允許重疊，即按照分區欄位同樣的資料不能同時儲存在不同的分區中）：
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;
ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESSTHAN(2500000);


在分區下建立新的子分區大致如下（RANGE分區，若為LIST或HASH分區，將建立方式修改為對應的方式即可）：
ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADDSUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);

4.5、修改分區名稱（修改相關的屬性資訊）:
ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TOMERGED_PARTITION02;

4.6、交換分區（快速交換資料，其實是交換段名稱指標）
  首先建立一個交換表，和原表結構相同，如果有資料，必須符合所交換對應分區的條件：
CREATE TABLE TABLE_PARTITION_2
  AS SELECT * FROM TABLE_PARTITION WHERE 1=2;
  然後將第一個分區的資料交換出去
ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01
  WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES; 
  此時會發現第一個分區的資料和表TABLE_PARTITION_2做了瞬間交換，比TRUNCATE還要快，因為這個過程沒有進行資料轉存，只是段名稱的修改過程，和實際的資料量沒有關係。

  如果是子分區也可以與外部的表進行交換，只需要將關鍵字修改為：SUBPARTITION 即可。

4.7、清空分區資料
   ALTER TABLE <table_name> TRUNCATE PARTITION<partition_name>;
   ALTER TABLE <table_name> TRUNCATE subpartition<subpartition_name>;

9、磁碟片段壓縮
   對分區表的某分區進行磁碟壓縮，當對分區內部資料進行了大量的UPDATE、DELETE操作後，一定時間需要進行磁碟壓縮，否則在查詢時，若通過FULL SCAN掃描資料，將會把空塊也會掃描到，對錶進行磁碟壓縮需要進行行遷移操作，所以首先需要操作：
ALTER TABLE <table_name> ENABLE ROW MOVEMENT ;


    對分區表的某分區壓縮文法為：
ALTER TABLE <table_name>
modify partition <partition_name> shrink space;
   對普通表壓縮：
ALTER TABLE <table_name> shrink space;
  對於索引也需要進行壓縮，索引也是表：
ALTER INDEX <index_name> shrink space;

10、分區表重新分析以及索引重新分析
  對錶進行壓縮後，需要對錶和索引進行重新分析，對錶進行重新分析，一般有兩種方式：
  在ORACLE 10G以前，使用：
BEGIN
    dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));
  END;


  ORACLE 10G後，可以使用：
  ANALYZE TABLE <table_name> COMPUTE STATISTICS;

  索引重新分析，將上述兩種方式分別修改一下，如第一種可以使用：gather_index_stats，而第二種修改為：ANALYZE INDEX即可，不過一般比較常用的是重新編譯：
  對於分區表並進行了索引分割區的情況，需要對每個分區的索引進行重新編譯,這裡以LOCAL索引為例子（其每個索引的分區和表分區結構相同，預設分區名稱和表分區名稱相同）：
ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;
  對於全域索引，根據全域索引鎖定義的分區名稱修改即可，若沒有分區，和普通單表索引重新編譯方式相同：
ALTER INDEX <index_name> REBUILD;


11、關聯對象重新編譯,
  上述對錶、索引進行重新編譯，尤其對錶進行了壓縮後會產生行遷移，這個過程可能會導致一些視圖、過程對象的失效，此時要將其重新編譯一次。


12、擴充：HASH分區中，如果建立了新的分區，可以將其進行重新HASH分布：
ALTER TABLE <table_name> COALESCA PARTITION% 

5、迴歸總結：何時建分區，分區類別，索引，如何對應[url=]SQL[/url]

1、建立時機

     上述已經說明，2G以上的表，ORACLE推薦建立分區。
     分區的方式根據實際情況而定，才能提高整體效能。
     分區的欄位一定要是經常用以提取資料的欄位，否則會在提取過程中導致遍曆多個分區，這樣比沒有分區還要慢。
     分區欄位要選擇合適，資料較為均勻分布到各個分區，不要太多也不要太少，而且根據分區欄位可以很快定位到分區範圍。
     一般情況下，盡量然業務操作在同一個分區內部完成。

2、分區類別
    分區主要有RANGE、LIST、HASH；
     RANGE通過值的定界分割，也是最常用的分區，這種分區注意在一種變長數字字串中，很多人會導致認為是數字類型，而按照數字區分區，這樣會分布十分不均勻的現象發生。
     LIST是列舉方式進行分區，一般作為二級分區而存在（當然也可以自己分區，ORACLE 11G後在分區上也可以作為主要磁碟分割而存在），在RANGE基礎上，若資料需要繼續分區，並且在RANGE基礎上資料量較為固定，只是較大，可以按照一定規則進一步分區。  } 
     HASH只指定分區個數，分區細節由ORACLE完成，增加HASH分區可以重新分配資料。

     注意：分區欄位不能使用函數轉換後在分區，如，將某數字字串欄位，先TO_NUMER(COL_NAME)後分區。

 

3、索引類別
    大致分：GLOBAL索引和LOCAL索引，錢和可以分：GLOBAL不分區索引，和GLOBAL分區索引。

     GLOBAL不分區索引一般不太推薦，因為是用一顆大的索引樹來映射一個表，這個過程，這樣速度不見得比不分區快。
     GLOBAL分區索引，尋找資料若通過要通過索引，是先定位了索引內部的分區，然後在這個分區索引中找到ROWID，然後回表提取資料。
     LOCAL索引是和分區的個數逐個對應的，可以說先定位分區表的分區也可以說先定位索引的分區，因為他們是一一對應的，找到對應分區後，分區內部索引資料集合。

4、對應應用0 
     分區表、索引、分區索引，要利用其效能優勢，最基本就是要提取資料時，要通過它首先將資料的範圍縮小到一個即使做全盤掃描也不會太慢的情況。
      所以SQL一定要有分區上的這個欄位的一個WHERE條件，將資料迅速定位到分區內部，而且盡量定位到一個分區裡面（這個和建立分區的規則有關係）。
      建立分區本身不提要效能，要用好才可提高效能，在必要的RAC叢集中，若存在多分區提取資料，適當採用並行提取可以提高提取的速度。
      對於索引部分，這裡也只提到分區索引的建立方式以及常見索引的維護方式，對於索引原理理解後會更容易認識到提取資料時的技巧。