oracle分區表之hash分區表的使用及擴充_oracle

Hash分區是通過對分區鍵運用Hash演算法從而決定資料的分區歸屬。使用Hash分區有什麼優點呢？

常用的分區表所具有的優點：如提高資料可用行，減少管理負擔，改善語句效能等優點，hash分區同樣擁有。此外，由於Hash分區表是按分區鍵的hash計算結果來決定其分區的，而特定的分區鍵其hash值是固定的，也就是說Hash分區表的資料是按分區索引值來聚集的，同樣的分區鍵肯定在同一分區。
比如，在證券行業，我們經常查詢某一隻股票的K線，
假設表的結構如下：

複製代碼 代碼如下:

create table equity
(
id number,
trade_date date,
……);

Equity表可能會很大，對equity表的查詢通常都是指定id,查詢某一交易日期或者某段時期內的其他資訊。這種情況下我們需要如何為equity表選擇分區呢？
單從表本身結構來看，似乎trade_date列很適合被選擇用來作定界分割。但如果我們這樣分區的話，前面需求中的查詢：指定某一id,查詢其某一範圍內的交易資訊，比如看1年內的K線，則這種查詢常常需要跨分區。我們知道，對分區表作跨分區查詢，很多時候其效能並不會太好，特別是這種查詢很可能還要跨很多分區。
你也可能會說，我們再在id, trade_date列上建個索引不就行了，仔細想想是不是這樣呢？這時候的equity表中的資料是按trade_date值來聚集的，同樣trade_date值的資料常常在一個資料區塊中，這樣前面需求中所描述的查詢即使通過索引訪問，最終讀表時也常常是去讀離散的資料區塊，即每一條記錄需要對應讀一個表資料區塊。
如果建成Hash分區表，則資料按hash分區鍵聚集，就更適合需求中描述的查詢，因為同樣id的記錄必定在同一分區，同時，同樣 id值的記錄落在同一資料區塊的幾率也增大了，從而“一定程度上”減少了IO。
上面對hash分區減少IO的描述加了引號，因為僅依靠Hash分區表試圖實現大範圍減少IO操作是不現實的，特別是當equity表中記錄的股票數非常多時，同一股票發生在不同交易日的記錄在物理上也很難聚集到相同資料區塊中。實際上，如果我們在Hash分區的基礎上再對equity表採用IOT表的組織方式，則前面描述的查詢效能就可大為提高。IOT表不在該文討論的範圍之內，這裡就不作進一步討論了。
當我們決定使用Hash表之前，我們還需要確定我們的所選擇的分區索引值是連續分布的，或者接近連續分區，此外，分區的個數需要是2的整數冪，比如2,4,8… 這些要求是由Hash函數的特點決定的，這樣我們分區表的各個分區所包含的資料量才會比較平均。

Hash分區表的擴充：

Hash分區表是通過add partition命令來增加分區的。Oracle推薦分區的個數是2的冪，比如,2,4,8..等等，這樣可以確保資料在各個分區中分布比較均勻。當然，如前所述，還需要分區索引值是連續分布的，或接近連續分布。
增加新分區時，需要將一些原有的資料從舊的分區劃分到新的分區中，那麼這種資料劃分時來來源資料分割選擇遵循什麼原則呢？
要點如下：如果要增加的分區是第N個分區，大於等於N的最小2的整數冪為M，則當增加第N個分區時，這個分區的資料來源於分區N-M/2。
比如，現在有個Hash分區表共有100個分區，我們想為其增加一個分區，則它是101個分區，即上面公式中的N為101，而大於101的最小2的整數冪為128，則M為128，於是，這個101分區的資料來源就應該是101-128/2=37分區。
換個角度來說，當我們在增加第101分區的時候，是需要鎖定37分區的，因為我們需要將該分區中的部分資料插入到新的101分區中。
下面，我們用一個執行個體來驗證上面的說法，同時看看在實際操作中有什麼需要注意的事項：
Commodity表是我們系統中的一個大表，幾年前在為該表建立Hash分區表時，當時的DBA在選擇分區數時指定了100個分區：

複製代碼 代碼如下:

select TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name=\'COMMODITY\' order by PARTITION_POSITION;
TABLE_NAME PARTITION_POSITION PARTITION_NAME NUM_ROWS
-------------- ------------------ ---------------------- ----------
COMMODITY 1 COT_IND01_P1 4405650
COMMODITY 2 COT_IND01_P2 5046650
COMMODITY 3 COT_IND01_P3 5107550
……
COMMODITY 36 COT_IND01_P36 5718800
COMMODITY 37 COT_IND01_P37 9905200
COMMODITY 38 COT_IND01_P38 10118400
COMMODITY 39 COT_IND01_P39 10404950
COMMODITY 40 COT_IND01_P40 9730850
COMMODITY 41 COT_IND01_P41 9457300
COMMODITY 42 COT_IND01_P42 9717950
COMMODITY 43 COT_IND01_P43 9643900
COMMODITY 44 COT_IND01_P44 11138000
COMMODITY 45 COT_IND01_P45 9381300
COMMODITY 46 COT_IND01_P46 10101150
COMMODITY 47 COT_IND01_P47 8809950
COMMODITY 48 COT_IND01_P48 10611050
COMMODITY 49 COT_IND01_P49 10010600
COMMODITY 50 COT_IND01_P50 8252600
COMMODITY 51 COT_IND01_P51 9709900
COMMODITY 52 COT_IND01_P52 8983200
COMMODITY 53 COT_IND01_P53 9012750
COMMODITY 54 COT_IND01_P54 9310650
COMMODITY 55 COT_IND01_P55 8966450
COMMODITY 56 COT_IND01_P56 8832650
COMMODITY 57 COT_IND01_P57 9470600
COMMODITY 58 COT_IND01_P58 8932450
COMMODITY 59 COT_IND01_P59 9994850
COMMODITY 60 COT_IND01_P60 9617450
COMMODITY 61 COT_IND01_P61 10278850
COMMODITY 62 COT_IND01_P62 9277600
COMMODITY 63 COT_IND01_P63 8136300
COMMODITY 64 COT_IND01_P64 10064600
COMMODITY 65 COT_IND01_P65 3710900
……
COMMODITY 99 COT_IND01_P99 5273800
COMMODITY 100 COT_IND01_P100 5293350
100 rows selected.

查詢各個分區的資料分布，我們可以看到，從分區37 ~ 64的28個分區的記錄數大概是其他分區的兩倍。由於100不是2的整數冪，所以Oracle的hash函數是無法保證資料是平均分布的。我們為該表添加一個新的分區COT_IND01_P101:

複製代碼 代碼如下:

alter table nts_commodity_ts add partition COT_IND01_P101;
Table altered.
Elapsed: 00:06:58.52

收集統計資訊後查詢新的分區記錄數：

複製代碼 代碼如下:

select TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name=\'COMMODITY\' and partition_name in (\'COT_IOT_IND01_P37\',\'COT_IOT_IND01_P101\');

TABLE_NAME PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------ --------------------- ----------
COMMODITY 37 COT__IND01_P37 4905200
COMMODITY 101 COT_IND01_P101 5107550

這時，我們可以看到，分區37中的資料被接近於平分到了分區37和101中。
監控增加分區過程中session鎖的情況，我們發現期間有兩個對象被以exclusive模式鎖定了：

複製代碼 代碼如下:

SQL> select * from v$lock where sid=1239 and type=\'TM\' and LMODE=6 order by sid,lmode;
ADDR                KADDR          SID TY ID1    ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
FFFFFFFF7D764828 FFFFFFFF7D764888 1239 TM 4004126 0  6 0 72 2
FFFFFFFF7D764828 FFFFFFFF7D764888 1239 TM 4004063 0  6 0 72 2
它們分別是什麼對象呢？
select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from user_objects where object_id in (4004126,4004063)
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
--------------------- ------------------------------ ----------
COMMODITY COT_IND01_P100 4004126
COMMODITY COT_IND01_P37 4004063

可以看到，分區37和100都被鎖定了。鎖定37分區是意料中的事，因為要從該錶轉移資料。那為什麼要鎖定第100個分區，也就是最後一個分區呢？
我的理解是：新增加分區的位置101是由原分區表的分區數100確定的，如果在增加分區的過程中允許對原表最後一個分區100作DDL操作，如coalesce操作，則新加的101分區就不一定是從原來的分區37分配資料了，101分區本身應該是新的第100分區，這樣就引起混亂了。到這裡，你可能會說，按這理解，是不是其他的分區也應該鎖定呢？其實不用，因為hash分區表是不支援drop partition操作的，而只支援coalesce操作來實作類別似的操作，但coalesce只能從最後一個分區開始收縮。
瞭解了增加hash表分區過程中鎖資訊的實際指導意義是什麼呢？
繼續上例中的討論，由於分區37和最後一個分區100會被獨佔鎖定定，因此在添加分區過程中這兩個分區是不能作DML操作的，因為DML操作需要在分區上申請共用鎖定(mode為3)。也就是操作這兩個分區的應用會受到影響。
Hash表增加分區不會像其他類型分區表，如range分區那樣能夠迅速完成，因為這裡添加分區的過程中是要有IO操作的，要轉移資料到新的分區。其實這還不是最主要的，由於Hash表是根據分區鍵Hash函數值來決定分區的，添加分區的主要時間其實是花在了計算hash值上。在上面的測試中，添加新分區操作的消耗時間是6分58秒，從下面的10046統計資訊可以看到，其中6分鐘都是花在了CPU操作上，相信主要是Hash運算引起的。

[code]
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      328      0.17       0.27          0          0        148           0
Execute   1520    360.14     396.30     456820   11416202      26357    11565252
Fetch     1767      5.42      21.18      21421      26540          0        2862
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3615    365.73     417.76     478241   11442742      26505    11568114

該測試案例中分區COT_IND01_P37中共有接近1千萬條資料，耗時接近7分鐘，假設分區資料達到了1億條，則耗時應該在1個小時以上。如果我們的Hash分區數按Oracle的建議為2的整數冪，則我們在增加分區時是要增加原有分區一倍的新分區，比如原分區為128個，擴充的時候需要增加128個分區，乘以每次添加分區需要的時間，則為Hash表增加分區將是一個很恐怖的操作。
總之，Hash分區有其優勢，但也有嚴重的缺陷，比如這裡描述的分區擴充問題。因此在項目設計之初，我們就需要謹慎選擇分區數。但是隨著資料量的增加，我們又很難避免為分區表增加分區的操作，這種操作是很耗資源的操作，操作過程中由於鎖的問題會影響對原有某些分區的操作。但如果我們因為畏懼前面存在的問題拖著不作分區擴充，則越是往後，隨著資料量的增加，這種增加分區的操作越難以實施。