談一下如何設計Oracle 分區表

在談設計Oracle分區表之間先區分一下分區表和資料表空間的個概念：

資料表空間：資料表空間是一個或多個資料檔案的集合，所有資料對象都存放在指定的資料表空間中，但主要存放表，故稱資料表空間。

分區表：分區致力於解決支援極大表和索引的關鍵問題。它採用他們分解成較小和易於管理的稱為分區的片(piece)的方法。一旦分區被定義，SQL語句就可以訪問的操作某一個分區而不是整個表，因而提高管理的效率。分區對於資料倉儲應用程式非常有效，因為他們常常儲存和分析巨量的曆史資料。當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。

表分區的具體作用：

    Oracle的分區表功能通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。

    通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具。

     資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。

在什麼時候使用分區表呢？

    1、表的大小超過2GB。

    2、表中包含曆史資料，新的資料被增加都新的分區中。

表分區的優缺點：

    優點：

    1、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

    2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

    3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

    4、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能。

    缺點：

    1、分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。

    2、分區致力於解決支援極大表和索引的關鍵問題。它採用他們分解成較小和易於管理的稱為分區的片(piece)的方法。

分區表的分類 

    1、Range partitioning（定界分割） 
    2、Hash partitioning（雜湊分割） 
    3、List partitioning（列表分區） 
    4、Composite range-hash partitioning（範圍-雜湊組合分區） 
    5、 Composite range-list partitioning（範圍-列表組合分區）

何時選擇定界分割 
    必須可以將表的記錄按照某一列值的範圍進行劃分。你想處理一些資料，這些資料經常是屬於某一個範圍內，例如月份。如果資料能夠按照分區的範圍均勻分布的話，那會獲得最佳效能。如果資料分布很不均勻的話，你可能不得不選擇其他分區方式。

CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) PARTITION BY RANGE (sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa, PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc, PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd );

 

何時選擇HASH分區 
如果資料不容易用定界分割，但你想提升效能和表的易管理性。 Hash分區提供了一個在指定數量的分區內交叉均勻分布資料的方法。行根據分區鍵的hash值映射到相應分區中。建立和使用hash分區你可以靈活放置資料，可以通過交叉訪問在不同I/O裝置上的分區提升效能。

CREATE TABLE scubagear (id NUMBER, name VARCHAR2 (60)) PARTITION BY HASH (id) PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);

 

何時挑選清單分區 
使用LIST分區你可以直接控制某些資料對應到某些分區。你可以為某個分區指定不連續的分區鍵值。這不同於RANGE分區（用鍵值的範圍劃分分區），也不同於HASH分區（不能控制某行映射到哪個分區）。

CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR', 'WA'), PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'), PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'), PARTITION q1_southeast VALUES ('FL', 'GA'), PARTITION q1_northcentral VALUES ('SD', 'WI'), PARTITION q1_southcentral VALUES ('OK', 'TX'));

分區表的設計 
如何選擇分區的類型，如何選擇分區的列呢？在這之前你必須明確你的目的——易管理性和效能，你更注重哪個方面？分區表所影響的方面可以歸類為以下幾種：效能、易管理性、資料清理。 
下面分別說說分區表對每一項的具體影響， 
效能： 
這一般是分區的主要目的。分區將大表變成了小表，當where之後的條件體現分區欄位的具體值時，避免了全表掃描。 
易於管理： 
對於包含海量資料的大表，分區帶來的易於管理性是非常明顯的。當你建議一個基於非分區表的索引時，唯一的選擇就是建立整個索引。如果表被分區，你就可以根據分區並行為此表建立索引，例如：

alter index par_ind_01 reuild partition yy05; 

除此之外你還可以同時的做很多事情，像改變表所在資料表空間、匯出表，刪除表資料等等。

刪除資料： 
我們經常會需要刪除表的一些曆史資料，一般做法是delete，但是這會導致undo和redo的資訊快速增長，而且影響資料庫整體效能。這時我們就可以利用drop某個分區來完成此任務，例如：

alter table tab_a drop partition yy01; 

當一個表的分區被刪除，對應的local索引也同時被刪除。如果還存在著global索引，那麼它會變成unusable狀態。為了避免此事情的發生，你可以使用：

alter table tab_a drop partition yy01 update global indexes;