簡單ORACLE分割槽表、分割槽索引

CREATE INDEX  ON ();

2.2、對於分割槽表的分割槽索引:

建立語法為：

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_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 PARTITION TAB_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 LESS THAN(2500000);

在分割槽下建立新的子分割槽大致如下（RANGE分割槽，若為LIST或HASH分割槽，將建立方式修改為對應的方式即可）：

ALTER TABLE MODIFY PARTITION ADD SUBPARTITION VALUES LESS THAN(....);

4.5、修改分割槽名稱（修改相關的屬性資訊）

ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_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  TRUNCATE PARTITION ;

   ALTER TABLE  TRUNCATE subpartition ;

9、磁碟碎片壓縮

   對分割槽表的某分割槽進行磁碟壓縮，當對分割槽內部資料進行了大量的UPDATE、DELETE操作後，一定時間需要進行磁碟壓縮，否則在查詢時，若透過FULL SCAN掃描資料，將會把空塊也會掃描到，對錶進行磁碟壓縮需要進行行遷移操作，所以首先需要操作：

ALTER TABLE  ENABLE ROW MOVEMENT ;

    對分割槽表的某分割槽壓縮語法為：

10、分割槽表重新分析以及索引重新分析

  對錶進行壓縮後，需要對錶和索引進行重新分析，對錶進行重新分析，一般有兩種方式：

  在ORACLE 10G以前，使用：

  BEGIN
     dbms_stats.gather_table_stats(USER,UPPER(''));
  END;

  ORACLE 10G後，可以使用：

  ANALYZE TABLE COMPUTE STATISTICS;

  索引重新分析，將上述兩種方式分別修改一下，如第一種可以使用：gather_index_stats，而第二種修改為：ANALYZE INDEX即可，不過一般比較常用的是重新編譯：

  對於分割槽表並進行了索引分割槽的情況，需要對每個分割槽的索引進行重新編譯,這裡以LOCAL索引為例子（其每個索引的分割槽和表分割槽結構相同，預設分割槽名稱和表分割槽名稱相同）：

 ALTER INDEX REBUILD PARTITION ;

  對於全域性索引，根據全域性索引鎖定義的分割槽名稱修改即可，若沒有分割槽，和普通單表索引重新編譯方式相同：

 ALTER INDEX REBUILD;

11、關聯物件重新編譯

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

12、擴充套件：HASH分割槽中，如果建立了新的分割槽，可以將其進行重新HASH分佈：

ALTER TABLE  COALESCA PARTITION

5、迴歸總結：何時建分割槽，分割槽類別，索引，如何對應SQL

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、對應應用

     分割槽表、索引、分割槽索引，要利用其效能優勢，最基本就是要提取資料時，要透過它首先將資料的範圍縮小到一個即使做全盤掃描也不會太慢的情況。

      所以SQL一定要有分割槽上的這個欄位的一個WHERE條件，將資料迅速定位到分割槽內部，而且儘量定位到一個分割槽裡面（這個和建立分割槽的規則有關係）。

      建立分割槽本身不提要效能，要用好才可提高效能，在必要的RAC叢集中，若存在多分割槽提取資料，適當採用並行提取可以提高提取的速度。

      對於索引部分，這裡也只提到分割槽索引的建立方式以及常見索引的維護方式，對於索引原理理解後會更容易認識到提取資料時的技巧。