oracle分區(qū)表之hash分區(qū)表的使用及擴展

2020-07-26 14:21:31

字體：大中小

供稿：網(wǎng)友

Hash分區(qū)是通過對分區(qū)鍵運用Hash算法從而決定數(shù)據(jù)的分區(qū)歸屬。使用Hash分區(qū)有什么優(yōu)點呢？

常用的分區(qū)表所具有的優(yōu)點：如提高數(shù)據(jù)可用行，減少管理負擔，改善語句性能等優(yōu)點，hash分區(qū)同樣擁有。此外，由于Hash分區(qū)表是按分區(qū)鍵的hash計算結(jié)果來決定其分區(qū)的，而特定的分區(qū)鍵其hash值是固定的，也就是說Hash分區(qū)表的數(shù)據(jù)是按分區(qū)鍵值來聚集的，同樣的分區(qū)鍵肯定在同一分區(qū)。
比如，在證券行業(yè)，我們經(jīng)常查詢某一只股票的K線，
假設(shè)表的結(jié)構(gòu)如下：

復(fù)制代碼代碼如下:

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

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

Hash分區(qū)表的擴展：

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

復(fù)制代碼代碼如下:

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.

查詢各個分區(qū)的數(shù)據(jù)分布，我們可以看到，從分區(qū)37 ~ 64的28個分區(qū)的記錄數(shù)大概是其他分區(qū)的兩倍。由于100不是2的整數(shù)冪，所以O(shè)racle的hash函數(shù)是無法保證數(shù)據(jù)是平均分布的。我們?yōu)樵摫硖砑右粋€新的分區(qū)COT_IND01_P101:

復(fù)制代碼代碼如下:

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

收集統(tǒng)計信息后查詢新的分區(qū)記錄數(shù)：

復(fù)制代碼代碼如下:

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

這時，我們可以看到，分區(qū)37中的數(shù)據(jù)被接近于平分到了分區(qū)37和101中。
監(jiān)控增加分區(qū)過程中session鎖的情況，我們發(fā)現(xiàn)期間有兩個對象被以exclusive模式鎖定了：

復(fù)制代碼代碼如下:

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

可以看到，分區(qū)37和100都被鎖定了。鎖定37分區(qū)是意料中的事，因為要從該表轉(zhuǎn)移數(shù)據(jù)。那為什么要鎖定第100個分區(qū)，也就是最后一個分區(qū)呢？
我的理解是：新增加分區(qū)的位置101是由原分區(qū)表的分區(qū)數(shù)100確定的，如果在增加分區(qū)的過程中允許對原表最后一個分區(qū)100作DDL操作，如coalesce操作，則新加的101分區(qū)就不一定是從原來的分區(qū)37分配數(shù)據(jù)了，101分區(qū)本身應(yīng)該是新的第100分區(qū)，這樣就引起混亂了。到這里，你可能會說，按這理解，是不是其他的分區(qū)也應(yīng)該鎖定呢？其實不用，因為hash分區(qū)表是不支持drop partition操作的，而只支持coalesce操作來實現(xiàn)類似的操作，但coalesce只能從最后一個分區(qū)開始收縮。
了解了增加hash表分區(qū)過程中鎖信息的實際指導(dǎo)意義是什么呢？
繼續(xù)上例中的討論，由于分區(qū)37和最后一個分區(qū)100會被排他鎖定，因此在添加分區(qū)過程中這兩個分區(qū)是不能作DML操作的，因為DML操作需要在分區(qū)上申請共享鎖(mode為3)。也就是操作這兩個分區(qū)的應(yīng)用會受到影響。
Hash表增加分區(qū)不會像其他類型分區(qū)表，如range分區(qū)那樣能夠迅速完成，因為這里添加分區(qū)的過程中是要有IO操作的，要轉(zhuǎn)移數(shù)據(jù)到新的分區(qū)。其實這還不是最主要的，由于Hash表是根據(jù)分區(qū)鍵Hash函數(shù)值來決定分區(qū)的，添加分區(qū)的主要時間其實是花在了計算hash值上。在上面的測試中，添加新分區(qū)操作的消耗時間是6分58秒，從下面的10046統(tǒng)計信息可以看到，其中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

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

上一篇：oracle10g發(fā)送email示例

下一篇：修改計算機名或IP后Oracle10g服務(wù)無法啟動的解決方法